Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports System.IO
Imports AppsUtility.EncDec
Imports System.Text
Imports System.Security.Cryptography
Public Class HRdataLogic
    Dim util As New AppsUtility.EncDec
    Public strCon As String = util.toDecrypt(System.Configuration.ConfigurationSettings.AppSettings("SQLString"))
    Public strConbio As String = util.toDecrypt(System.Configuration.ConfigurationSettings.AppSettings("SQLStringforbioadmin"))
    'Private iv() As Byte = {26, 65, 69, 110, 68, 178, 219, 200}
    Private key() As Byte = {14, 1, 15, 2, 16, 3, 17, 4, 18, 5, 19, 6, 20, 7, 21, 8, 22, 9, 23, 10, 24, 11, 13, 12}

    Public Function ListEmployee(ByVal ThisCompanyID As Integer, ByVal GroupID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If ThisCompanyID = 1 Then
            ThisCompanyID = 0
        End If
        If ThisCompanyID = 0 Then
            If GroupID = 0 Then
                strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpName,a.EmpMobile+'/'+EmpResPhone EmpPhone," & _
                        "a.EmpBankID,a.EmpBankBranch,a.EmpBankAccount,GroupName," & _
                        "a.EmpSex,a.EmpAppointmentDate,b.NationalityName " & _
                        "from Employee a,Nationality b,EmployeeGroup c " & _
                        "where a.EmpNationalityID=b.NationalityID and a.EmpCategory=c.GroupID " & _
                        "and ltrim(rtrim(a.EmpStatus))='ACTIVE' " & _
                        "order by a.EmpPFnumber "
            Else
                strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpName,a.EmpMobile+'/'+EmpResPhone EmpPhone," & _
                        "a.EmpBankID,a.EmpBankBranch,a.EmpBankAccount,GroupName," & _
                        "a.EmpSex,a.EmpAppointmentDate,b.NationalityName " & _
                        "from Employee a,Nationality b,EmployeeGroup c " & _
                        "where a.EmpNationalityID=b.NationalityID and a.EmpCategory=c.GroupID " & _
                        "and ltrim(rtrim(a.EmpStatus))='ACTIVE' and c.GroupID=" & GroupID & _
                        " order by a.EmpPFnumber "
            End If
        Else
            If GroupID = 0 Then
                strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpName,a.EmpMobile+'/'+EmpResPhone EmpPhone," & _
                        "a.EmpBankID,a.EmpBankBranch,a.EmpBankAccount,GroupName," & _
                        "a.EmpSex,a.EmpAppointmentDate,b.NationalityName " & _
                        "from Employee a,Nationality b,EmployeeGroup c " & _
                        "where a.EmpNationalityID=b.NationalityID and a.EmpCategory=c.GroupID " & _
                        "and ltrim(rtrim(a.EmpStatus))='ACTIVE' and a.EmpCompany=" & ThisCompanyID & _
                        "order by a.EmpPFnumber "
            Else
                strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpName,a.EmpMobile+'/'+EmpResPhone EmpPhone," & _
                        "a.EmpBankID,a.EmpBankBranch,a.EmpBankAccount,GroupName," & _
                        "a.EmpSex,a.EmpAppointmentDate,b.NationalityName " & _
                        "from Employee a,Nationality b,EmployeeGroup c " & _
                        "where a.EmpNationalityID=b.NationalityID and a.EmpCategory=c.GroupID " & _
                        "and ltrim(rtrim(a.EmpStatus))='ACTIVE' and c.GroupID=" & GroupID & " and a.EmpCompany=" & ThisCompanyID & _
                        "order by a.EmpPFnumber "
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        ListEmployee = ds
    End Function
    Public Function ListEmployeeDocExpiry(ByVal ThisCompanyID As Integer, ByVal TypeID As Integer, ByVal ExpDays As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If ThisCompanyID = 1 Then
            ThisCompanyID = 0
        End If
        If ThisCompanyID = 0 Then
            If TypeID = 1 Then
                strsql = "select EmpID,EmpPFnumber,EmpName,EmpMobile,EmpCIDnumber,EmpCIDexpiry,DATEDIFF(day,getdate(), EmpCidExpiry ) DaysToExpire" & _
                        " from Employee where EmpStatus='ACTIVE' and abs(DATEDIFF(day, EmpCidExpiry, getdate()))<= " & ExpDays & _
                        " order by EmpName "
            Else
                strsql = "select EmpID,EmpPFnumber,EmpName,EmpMobile,EmpPPnumber,EmpPPexpiry,DATEDIFF(day,getdate(), EmpPPExpiry ) DaysToExpire" & _
                        " from Employee where EmpStatus='ACTIVE' and abs(DATEDIFF(day, EmpPPExpiry, getdate()))<= " & ExpDays & _
                        " order by EmpName "
            End If
        Else
            If TypeID = 1 Then
                strsql = "select EmpID,EmpPFnumber,EmpName,EmpMobile,EmpCIDnumber,EmpCIDexpiry,DATEDIFF(day,getdate(), EmpCidExpiry ) DaysToExpire" & _
                        " from Employee where EmpStatus='ACTIVE' and abs(DATEDIFF(day, EmpCidExpiry, getdate()))<= " & ExpDays & _
                        " and EmpCompany=" & ThisCompanyID & " order by EmpName "
            Else
                strsql = "select EmpID,EmpPFnumber,EmpName,EmpMobile,EmpPPnumber,EmpPPexpiry,DATEDIFF(day,getdate(), EmpPPExpiry ) DaysToExpire" & _
                        " from Employee where EmpStatus='ACTIVE' and abs(DATEDIFF(day, EmpPPExpiry, getdate()))<= " & ExpDays & _
                        " and EmpCompany=" & ThisCompanyID & " order by EmpName "
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        ListEmployeeDocExpiry = ds
    End Function


    Public Sub DeleteEmployee(ByVal EmployeeID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Update Employee set EmpStatus='DELETED' where EmpID=" & EmployeeID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Function GetHolidaysPrompt(ByVal MonthCode As Integer, ByVal YearCode As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String

        strsql = "select DayID,ActivityDate,HolidayFlag " & _
            "from HolidayCalendar " & _
            "where YearCode=" & YearCode & " and MonthCode=" & MonthCode & _
            "order by DayID "
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SUPPLIER")
        Try
            da.Fill(ds, "SUPPLIER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetHolidaysPrompt = ds
    End Function
    Public Function ListEmployeeBenefit(ByVal GroupID As Integer, ByVal ThisCompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If ThisCompanyID = 1 Then
            ThisCompanyID = 0
        End If
        If ThisCompanyID = 0 Then
            If GroupID = 0 Then
                strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                        "a.EmpAppointmentDate,GroupName EmpCategory, " & _
                        "isnull(BasicSalary,0) BasicSalary,isnull(Allowance,0) Allowance," & _
                        "isnull(HouseRent,0) HouseRent,isnull(Conveyance,0) Conveyance," & _
                        "isnull(OverTimeAmount,0) OverTimeAmount," & _
                        "isnull(Loan1Amount,0) Loan1Amount,isnull(Loan1Installment,0) Loan1Installment," & _
                        "isnull(Loan2Amount,0) Loan2Amount,isnull(Loan2Installment,0) Loan2Installment," & _
                        "isnull(Loan3Amount,0) Loan3Amount,isnull(Loan3Installment,0) Loan3Installment," & _
                        "isnull(Loan4Amount,0) Loan4Amount,isnull(Loan4Installment,0) Loan4Installment," & _
                        "0.000 Gross,0.000 Net,isnull(DaysWorked,0) DaysWorked   " & _
                        "from Employee a,EmployeeBenefit b,EmployeeGroup c " & _
                        "where a.EmpCategory=c.GroupID and a.EmpID=b.EmpID " & _
                        "and ltrim(rtrim(a.EmpStatus))='ACTIVE' " & _
                        " order by a.EmpPFnumber "
            Else
                strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                        "a.EmpAppointmentDate,GroupName EmpCategory, " & _
                        "isnull(BasicSalary,0) BasicSalary,isnull(Allowance,0) Allowance," & _
                        "isnull(HouseRent,0) HouseRent,isnull(Conveyance,0) Conveyance," & _
                        "isnull(OverTimeAmount,0) OverTimeAmount," & _
                        "isnull(Loan1Amount,0) Loan1Amount,isnull(Loan1Installment,0) Loan1Installment," & _
                        "isnull(Loan2Amount,0) Loan2Amount,isnull(Loan2Installment,0) Loan2Installment," & _
                        "isnull(Loan3Amount,0) Loan3Amount,isnull(Loan3Installment,0) Loan3Installment," & _
                        "isnull(Loan4Amount,0) Loan4Amount,isnull(Loan4Installment,0) Loan4Installment," & _
                        "0.000 Gross,0.000 Net,isnull(DaysWorked,0) DaysWorked   " & _
                        "from Employee a,EmployeeBenefit b,EmployeeGroup c " & _
                        "where a.EmpCategory=c.GroupID and a.EmpID=b.EmpID " & _
                        "and ltrim(rtrim(a.EmpStatus))='ACTIVE' " & _
                        " and a.EmpCategory=" & GroupID & _
                        "order by a.EmpPFnumber "
            End If
        Else
            If GroupID = 0 Then
                strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                        "a.EmpAppointmentDate,GroupName EmpCategory, " & _
                        "isnull(BasicSalary,0) BasicSalary,isnull(Allowance,0) Allowance," & _
                        "isnull(HouseRent,0) HouseRent,isnull(Conveyance,0) Conveyance," & _
                        "isnull(OverTimeAmount,0) OverTimeAmount," & _
                        "isnull(Loan1Amount,0) Loan1Amount,isnull(Loan1Installment,0) Loan1Installment," & _
                        "isnull(Loan2Amount,0) Loan2Amount,isnull(Loan2Installment,0) Loan2Installment," & _
                        "isnull(Loan3Amount,0) Loan3Amount,isnull(Loan3Installment,0) Loan3Installment," & _
                        "isnull(Loan4Amount,0) Loan4Amount,isnull(Loan4Installment,0) Loan4Installment," & _
                        "0.000 Gross,0.000 Net,isnull(DaysWorked,0) DaysWorked   " & _
                        "from Employee a,EmployeeBenefit b,EmployeeGroup c " & _
                        "where a.EmpCategory=c.GroupID and a.EmpID=b.EmpID " & _
                        "and ltrim(rtrim(a.EmpStatus))='ACTIVE' and EmpCompany=" & ThisCompanyID & _
                        " order by a.EmpPFnumber "
            Else
                strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                        "a.EmpAppointmentDate,GroupName EmpCategory, " & _
                        "isnull(BasicSalary,0) BasicSalary,isnull(Allowance,0) Allowance," & _
                        "isnull(HouseRent,0) HouseRent,isnull(Conveyance,0) Conveyance," & _
                        "isnull(OverTimeAmount,0) OverTimeAmount," & _
                        "isnull(Loan1Amount,0) Loan1Amount,isnull(Loan1Installment,0) Loan1Installment," & _
                        "isnull(Loan2Amount,0) Loan2Amount,isnull(Loan2Installment,0) Loan2Installment," & _
                        "isnull(Loan3Amount,0) Loan3Amount,isnull(Loan3Installment,0) Loan3Installment," & _
                        "isnull(Loan4Amount,0) Loan4Amount,isnull(Loan4Installment,0) Loan4Installment," & _
                        "0.000 Gross,0.000 Net,isnull(DaysWorked,0) DaysWorked   " & _
                        "from Employee a,EmployeeBenefit b,EmployeeGroup c " & _
                        "where a.EmpCategory=c.GroupID and a.EmpID=b.EmpID " & _
                        "and ltrim(rtrim(a.EmpStatus))='ACTIVE' and EmpCompany=" & ThisCompanyID & _
                        " and a.EmpCategory=" & GroupID & _
                        "order by a.EmpPFnumber "
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try

        Dim drow As DataRow
        For Each drow In ds.Tables(0).Rows
            drow("BasicSalary") = Decrypt(drow("BasicSalary"))
            drow("Allowance") = Decrypt(drow("Allowance"))
            drow("HouseRent") = Decrypt(drow("HouseRent"))
            drow("Conveyance") = Decrypt(drow("Conveyance"))
            drow("OverTimeAmount") = Decrypt(drow("OverTimeAmount"))
            drow("Loan1Amount") = Decrypt(drow("Loan1Amount"))
            drow("Loan2Amount") = Decrypt(drow("Loan2Amount"))
            drow("Loan3Amount") = Decrypt(drow("Loan3Amount"))
            drow("Loan4Amount") = Decrypt(drow("Loan4Amount"))
            drow("Loan1Installment") = Decrypt(drow("Loan1Installment"))
            drow("Loan2Installment") = Decrypt(drow("Loan2Installment"))
            drow("Loan3Installment") = Decrypt(drow("Loan3Installment"))
            drow("Loan4Installment") = Decrypt(drow("Loan4Installment"))
        Next
        sqlCon.Close()
        ListEmployeeBenefit = ds
    End Function
    Public Function ListEmployeeAttendancewithIN_OUR_Stats(ByVal EmpID As Integer, ByVal dates As String) As DataSet

        Dim sqlConB As SqlConnection = New SqlConnection(strConbio)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim newdate As String

        Try

            newdate = Right(dates, 4) & "-" & dates.Substring(3, 2) & "-" & Left(dates, 2)
            'newdate = Left(dates, 2) & "/" & dates.Substring(3, 2) & "/" & Right(dates, 4)
            'newdate = Right(dates, 4) & "-" & Left(dates, 2) & "-" & dates.Substring(3, 2) ' for me

            strsql = "SELECT EmpPFnumber FROM Employee WHERE EmpID=" & EmpID
            Dim datadp As New SqlClient.SqlDataAdapter(strsql, sqlCon)
            Dim datset As New DataSet
            datadp.Fill(datset)
            'userinfo
            strsql = "SELECT dtDateTime,nDeviceID,nEvent,nUserID,nTNAEvent FROM tb_reportslist WHERE nUserID='" & datset.Tables(0).Rows(0)(0).ToString() & "' and dtDateTime BETWEEN '" & newdate & " 00:00:00.000' AND '" & newdate & " 23:59:59.999' and nEvent='55' ORDER BY dtDateTime "
            sqlCon.Open()
            sqlConB.Open()
            Dim da As New SqlClient.SqlDataAdapter(strsql, sqlConB)
            Dim ds As New DataSet("ATTENDANCE")
            da.Fill(ds, "ATTENDANCE")


            sqlCon.Close()
            sqlConB.Close()

            ListEmployeeAttendancewithIN_OUR_Stats = ds
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try

    End Function

    Public Function ListEmployeeAttendancewithIN_OUR_Stats_DPF(ByVal EmpID As Integer, ByVal dates As String, ByVal dID As Integer) As DataSet

        Dim sqlConB As SqlConnection = New SqlConnection(strConbio)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim newdate As String

        Try
            newdate = Right(dates, 4) & "-" & dates.Substring(3, 2) & "-" & Left(dates, 2)
            'newdate = Left(dates, 2) & "/" & dates.Substring(3, 2) & "/" & Right(dates, 4)
            'newdate = Right(dates, 4) & "-" & Left(dates, 2) & "-" & dates.Substring(3, 2) 'for me
            strsql = "SELECT EmpPFnumber FROM Employee WHERE EmpPFnumber=" & EmpID
            Dim datadp As New SqlClient.SqlDataAdapter(strsql, sqlCon)
            Dim datset As New DataSet
            datadp.Fill(datset)
            'userinfo
            'MessageBox.Show(newdate)
            If dID = 0 Then
                strsql = "SELECT dtDateTime,nDeviceID,nEvent,nUserID,nTNAEvent FROM tb_reportslist WHERE nUserID='" & datset.Tables(0).Rows(0)(0).ToString() & "' and dtDateTime BETWEEN '" & newdate & " 00:00:00' AND '" & newdate & " 23:59:59' and nEvent='55' ORDER BY dtDateTime"
            Else
                strsql = "SELECT dtDateTime,nDeviceID,nEvent,nUserID,nTNAEvent FROM tb_reportslist WHERE nUserID='" & datset.Tables(0).Rows(0)(0).ToString() & "' and nDeviceID = " & dID & " and dtDateTime BETWEEN '" & newdate & " 00:00:00' AND '" & newdate & " 23:59:59' and nEvent='55' ORDER BY dtDateTime"
            End If
            sqlCon.Open()
            sqlConB.Open()
            Dim da As New SqlClient.SqlDataAdapter(strsql, sqlConB)
            Dim ds As New DataSet("ATTENDANCE")
            da.Fill(ds, "ATTENDANCE")


            sqlCon.Close()
            sqlConB.Close()

            ListEmployeeAttendancewithIN_OUR_Stats_DPF = ds
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try



    End Function
    Public Function ListEmployeeAttendancewithIN_OUR_Stats_withPF(ByVal EmpID As Integer, ByVal dates As String, ByVal dID As Integer) As DataSet

        Dim sqlConB As SqlConnection = New SqlConnection(strConbio)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim newdate As String
        'MessageBox.Show(dID)
        Try
            newdate = Right(dates, 4) & "-" & dates.Substring(3, 2) & "-" & Left(dates, 2)
            'newdate = Left(dates, 2) & "/" & dates.Substring(3, 2) & "/" & Right(dates, 4)
            'newdate = Right(dates, 4) & "-" & Left(dates, 2) & "-" & dates.Substring(3, 2) 'for me
            strsql = "SELECT EmpPFnumber FROM Employee WHERE EmpID='" & EmpID & "'"
            Dim datadp As New SqlClient.SqlDataAdapter(strsql, sqlCon)
            Dim datset As New DataSet
            datadp.Fill(datset)
            'userinfo
            If dID = 0 Then
                strsql = "SELECT dtDateTime,nDeviceID,nEvent,nUserID,nTNAEvent FROM tb_reportslist WHERE nUserID=" & EmpID & " and dtDateTime BETWEEN '" & newdate & " 00:00:00.000' AND '" & newdate & " 23:59:59.999' and nEvent=55 ORDER BY dtDateTime "

            Else
                strsql = "SELECT dtDateTime,nDeviceID,nEvent,nUserID,nTNAEvent FROM tb_reportslist WHERE nUserID=" & EmpID & " and nDeviceID = " & dID & " and dtDateTime BETWEEN '" & newdate & " 00:00:00.000' AND '" & newdate & " 23:59:59.999' and nEvent=55 ORDER BY dtDateTime "

            End If
            sqlCon.Open()
            sqlConB.Open()
            Dim da As New SqlClient.SqlDataAdapter(strsql, sqlConB)
            Dim ds As New DataSet("ATTENDANCE")
            da.Fill(ds, "ATTENDANCE")

            sqlCon.Close()
            sqlConB.Close()

            ListEmployeeAttendancewithIN_OUR_Stats_withPF = ds
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try



    End Function

    Public Function Calculate_TimeIntervals_btwnTimeStamp(ByVal fromdate As String, ByVal todate As String) As String
        Dim timeinterval As TimeSpan

        timeinterval = CDate(todate).Subtract(CDate(fromdate))

        Calculate_TimeIntervals_btwnTimeStamp = timeinterval.ToString()

    End Function
    Public Function GetEmployees_B_G(ByVal gid As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select EmpID,EmpName,EmpPFnumber from Employee where ltrim(rtrim(EmpStatus))='ACTIVE' AND EmpCategory = " & gid & " order by EmpName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetEmployees_B_G = ds
    End Function
    Public Function ListEmployeeAttendance(ByVal EmpID As Integer, ByVal FromDate As String, ByVal ToDate As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        FromDate = Right(FromDate, 4) & "/" & FromDate.Substring(3, 2) & "/" & Left(FromDate, 2)
        ToDate = Right(ToDate, 4) & "/" & ToDate.Substring(3, 2) & "/" & Left(ToDate, 2)
        strsql = "select a.EmpID,a.EmpPfNumber,' ' ThisDay,' ' ThisType," & _
                "EmpDate,EmpDayType,right(EmpDayIn,8) EmpDayIn,right(EmpBreakOut,8) EmpBreakOut," & _
                "right(EmpBreakIn,8) EmpBreakIn,right(EmpDayOut,8) EmpDayOut,EmpHoursWorked " & _
                "from Employee a,EmployeeAttendance b " & _
                "where a.EmpID=b.EmpID " & _
                "and EmpDate between '" & FromDate & "' and '" & ToDate & "' " & _
                "and a.EmpID=" & EmpID & " order by b.EmpDate "
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
            Dim drow As DataRow
            For Each drow In ds.Tables(0).Rows
                FromDate = drow("EmpDate")
                FromDate = Right(FromDate, 2) & "/" & FromDate.Substring(5, 2) & "/" & Left(FromDate, 4)
                drow("EmpDate") = FromDate
                drow("ThisDay") = CDate(FromDate).DayOfWeek
                Select Case (drow("EmpDayType"))
                    Case Is = 1
                        drow("ThisType") = "Normal"
                    Case Is = 2
                        drow("ThisType") = "WeeklyOff"
                    Case Is = 3
                        drow("ThisType") = "Holiday"
                End Select
            Next
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        sqlCon.Close()
        ListEmployeeAttendance = ds
    End Function
    Public Function Getmydates(ByVal FromDate As String, ByVal ToDate As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        FromDate = Right(FromDate, 4) & "/" & FromDate.Substring(3, 2) & "/" & Left(FromDate, 2)
        ToDate = Right(ToDate, 4) & "/" & ToDate.Substring(3, 2) & "/" & Left(ToDate, 2)

        Dim mydates As New DataSet
        mydates.Tables.Add("results")
        mydates.Tables("results").Columns.Add("froms")
        mydates.Tables("results").Columns.Add("tos")

        Dim newRow As DataRow = mydates.Tables("results").NewRow()
        newRow("froms") = FromDate
        newRow("tos") = ToDate
        mydates.Tables("results").Rows.Add(newRow)

    End Function

    Public Function ListEmployeeBenefitForPayroll(ByVal GroupID As Integer, ByVal ThisCompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        'If ThisCompanyID = 1 Then
        '    ThisCompanyID = 0
        'End If
        If ThisCompanyID = 0 Then
            If GroupID = 0 Then
                strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                        "a.EmpAppointmentDate,GroupName EmpCategory, " & _
                        "isnull(BasicSalary,0) BasicSalary,isnull(Allowance,0) Allowance," & _
                        "isnull(HouseRent,0) HouseRent,isnull(Conveyance,0) Conveyance," & _
                        "isnull(OverTimeAmount,0) OverTimeAmount," & _
                        "isnull(Loan1Amount,0) Loan1Amount,isnull(Loan1Installment,0) Loan1Installment," & _
                        "isnull(Loan2Amount,0) Loan2Amount,isnull(Loan2Installment,0) Loan2Installment," & _
                        "isnull(Loan3Amount,0) Loan3Amount,isnull(Loan3Installment,0) Loan3Installment," & _
                        "isnull(Loan4Amount,0) Loan4Amount,isnull(Loan4Installment,0) Loan4Installment," & _
                        "0.000 Gross,0.000 Net,isnull(DaysWorked,0) DaysWorked   " & _
                        "from Employee a,EmployeeBenefit b,EmployeeGroup c " & _
                        "where a.EmpCategory=c.GroupID and a.EmpID=b.EmpID " & _
                        "and ltrim(rtrim(a.EmpStatus))='ACTIVE' " & _
                        " order by a.EmpName "
            Else
                strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                        "a.EmpAppointmentDate,GroupName EmpCategory, " & _
                        "isnull(BasicSalary,0) BasicSalary,isnull(Allowance,0) Allowance," & _
                        "isnull(HouseRent,0) HouseRent,isnull(Conveyance,0) Conveyance," & _
                        "isnull(OverTimeAmount,0) OverTimeAmount," & _
                        "isnull(Loan1Amount,0) Loan1Amount,isnull(Loan1Installment,0) Loan1Installment," & _
                        "isnull(Loan2Amount,0) Loan2Amount,isnull(Loan2Installment,0) Loan2Installment," & _
                        "isnull(Loan3Amount,0) Loan3Amount,isnull(Loan3Installment,0) Loan3Installment," & _
                        "isnull(Loan4Amount,0) Loan4Amount,isnull(Loan4Installment,0) Loan4Installment," & _
                        "0.000 Gross,0.000 Net,isnull(DaysWorked,0) DaysWorked   " & _
                        "from Employee a,EmployeeBenefit b,EmployeeGroup c " & _
                        "where a.EmpCategory=c.GroupID and a.EmpID=b.EmpID " & _
                        "and ltrim(rtrim(a.EmpStatus))='ACTIVE' " & _
                        " and a.EmpCategory=" & GroupID & _
                        "order by a.EmpName "
            End If
        Else
            If GroupID = 0 Then
                strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                        "a.EmpAppointmentDate,GroupName EmpCategory, " & _
                        "isnull(BasicSalary,0) BasicSalary,isnull(Allowance,0) Allowance," & _
                        "isnull(HouseRent,0) HouseRent,isnull(Conveyance,0) Conveyance," & _
                        "isnull(OverTimeAmount,0) OverTimeAmount," & _
                        "isnull(Loan1Amount,0) Loan1Amount,isnull(Loan1Installment,0) Loan1Installment," & _
                        "isnull(Loan2Amount,0) Loan2Amount,isnull(Loan2Installment,0) Loan2Installment," & _
                        "isnull(Loan3Amount,0) Loan3Amount,isnull(Loan3Installment,0) Loan3Installment," & _
                        "isnull(Loan4Amount,0) Loan4Amount,isnull(Loan4Installment,0) Loan4Installment," & _
                        "0.000 Gross,0.000 Net,isnull(DaysWorked,0) DaysWorked   " & _
                        "from Employee a,EmployeeBenefit b,EmployeeGroup c " & _
                        "where a.EmpCategory=c.GroupID and a.EmpID=b.EmpID " & _
                        "and ltrim(rtrim(a.EmpStatus))='ACTIVE' and EmpCompany=" & ThisCompanyID & _
                        " order by a.EmpName "
            Else
                strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                        "a.EmpAppointmentDate,GroupName EmpCategory, " & _
                        "isnull(BasicSalary,0) BasicSalary,isnull(Allowance,0) Allowance," & _
                        "isnull(HouseRent,0) HouseRent,isnull(Conveyance,0) Conveyance," & _
                        "isnull(OverTimeAmount,0) OverTimeAmount," & _
                        "isnull(Loan1Amount,0) Loan1Amount,isnull(Loan1Installment,0) Loan1Installment," & _
                        "isnull(Loan2Amount,0) Loan2Amount,isnull(Loan2Installment,0) Loan2Installment," & _
                        "isnull(Loan3Amount,0) Loan3Amount,isnull(Loan3Installment,0) Loan3Installment," & _
                        "isnull(Loan4Amount,0) Loan4Amount,isnull(Loan4Installment,0) Loan4Installment," & _
                        "0.000 Gross,0.000 Net,isnull(DaysWorked,0) DaysWorked   " & _
                        "from Employee a,EmployeeBenefit b,EmployeeGroup c " & _
                        "where a.EmpCategory=c.GroupID and a.EmpID=b.EmpID " & _
                        "and ltrim(rtrim(a.EmpStatus))='ACTIVE' and EmpCompany=" & ThisCompanyID & _
                        " and a.EmpCategory=" & GroupID & _
                        "order by a.EmpName "
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try

        Dim drow As DataRow
        For Each drow In ds.Tables(0).Rows
            drow("BasicSalary") = Decrypt(drow("BasicSalary"))
            drow("Allowance") = Decrypt(drow("Allowance"))
            drow("HouseRent") = Decrypt(drow("HouseRent"))
            drow("Conveyance") = Decrypt(drow("Conveyance"))
            drow("OverTimeAmount") = Decrypt(drow("OverTimeAmount"))
            drow("Loan1Amount") = Decrypt(drow("Loan1Amount"))
            drow("Loan2Amount") = Decrypt(drow("Loan2Amount"))
            drow("Loan3Amount") = Decrypt(drow("Loan3Amount"))
            drow("Loan4Amount") = Decrypt(drow("Loan4Amount"))
            drow("Loan1Installment") = Decrypt(drow("Loan1Installment"))
            drow("Loan2Installment") = Decrypt(drow("Loan2Installment"))
            drow("Loan3Installment") = Decrypt(drow("Loan3Installment"))
            drow("Loan4Installment") = Decrypt(drow("Loan4Installment"))
        Next
        sqlCon.Close()
        ListEmployeeBenefitForPayroll = ds
    End Function
    Public Function ListEmployeePayroll(ByVal ThisCompanyID As Integer, ByVal MonthCode As Integer, ByVal YearCode As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        'If ThisCompanyID = 1 Then
        '    ThisCompanyID = 0
        'End If
        If ThisCompanyID = 0 Then
            strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                    "a.EmpAppointmentDate, " & _
                    "BasicSalary,Allowance,HouseRent,Conveyance,OverTimeAmount," & _
                    "Deduction1,Deduction2,Deduction3,Deduction4,0.000 TotalInstallment,ProrataDeduction,TotalDeduction,GrossAmount Gross, NetAmount Net,DaysWorked,TotalDays,TotalDays-DaysWorked AbsentDays " & _
                    "from Employee a,EmployeePayroll b " & _
                    "where  a.EmpID=b.EmpID " & _
                    "and ltrim(rtrim(a.EmpStatus))='ACTIVE' " & _
                    "order by a.EmpName "
        Else
            strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                    "a.EmpAppointmentDate, " & _
                    "BasicSalary,Allowance,HouseRent,Conveyance,OverTimeAmount," & _
                    "Deduction1,Deduction2,Deduction3,Deduction4,0.000 TotalInstallment,ProrataDeduction,TotalDeduction,GrossAmount Gross, NetAmount Net,DaysWorked,TotalDays,TotalDays-DaysWorked AbsentDays " & _
                    "from Employee a,EmployeePayroll b " & _
                    "where  a.EmpID=b.EmpID " & _
                    "and ltrim(rtrim(a.EmpStatus))='ACTIVE' and EmpCompany=" & ThisCompanyID & _
                    " order by a.EmpName "
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim drow As DataRow
        For Each drow In ds.Tables(0).Rows
            drow("BasicSalary") = Decrypt(drow("BasicSalary"))
            drow("Allowance") = Decrypt(drow("Allowance"))
            drow("HouseRent") = Decrypt(drow("HouseRent"))
            drow("Conveyance") = Decrypt(drow("Conveyance"))
            drow("OverTimeAmount") = Decrypt(drow("OverTimeAmount"))
            drow("Deduction1") = Decrypt(drow("Deduction1"))
            drow("Deduction2") = Decrypt(drow("Deduction2"))
            drow("Deduction3") = Decrypt(drow("Deduction3"))
            drow("Deduction4") = Decrypt(drow("Deduction4"))
            drow("TotalInstallment") = drow("Deduction1") + drow("Deduction2") + drow("Deduction3") + drow("Deduction4")
            drow("TotalDeduction") = Decrypt(drow("TotalDeduction"))
            drow("ProrataDeduction") = Decrypt(drow("ProrataDeduction"))
            drow("Gross") = Decrypt(drow("Gross"))
            drow("Net") = Decrypt(drow("Net"))
        Next
        sqlCon.Close()
        ListEmployeePayroll = ds
    End Function
    Public Function ListEmployeePayrollForReport(ByVal ThisCompanyID As Integer, ByVal MonthCode As Integer, ByVal YearCode As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If ThisCompanyID = 1 Then
            ThisCompanyID = 0
        End If
        If ThisCompanyID = 0 Then
            strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                    "a.EmpAppointmentDate, " & _
                    "BasicSalary,Allowance,HouseRent,Conveyance,OverTimeAmount," & _
                    "Deduction1,Deduction2,Deduction3,Deduction4,0.000 TotalInstallment,ProrataDeduction,TotalDeduction,GrossAmount Gross, NetAmount Net,DaysWorked,TotalDays,TotalDays-DaysWorked AbsentDays " & _
                    "from Employee a,EmployeePayroll b " & _
                    "where  a.EmpID=b.EmpID and YearCode='" & YearCode & "' and MonthCode='" & MonthCode & "' " & _
                    "and ltrim(rtrim(a.EmpStatus))='ACTIVE' " & _
                    "order by a.EmpPFnumber "
        Else
            strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                    "a.EmpAppointmentDate, " & _
                    "BasicSalary,Allowance,HouseRent,Conveyance,OverTimeAmount," & _
                    "Deduction1,Deduction2,Deduction3,Deduction4,0.000 TotalInstallment,ProrataDeduction,TotalDeduction,GrossAmount Gross, NetAmount Net,DaysWorked,TotalDays,TotalDays-DaysWorked AbsentDays " & _
                    "from Employee a,EmployeePayroll b " & _
                    "where  a.EmpID=b.EmpID and YearCode='" & YearCode & "' and MonthCode='" & MonthCode & "' " & _
                    "and ltrim(rtrim(a.EmpStatus))='ACTIVE' and EmpCompany=" & ThisCompanyID & _
                    " order by a.EmpPFNumber "
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim drow As DataRow
        For Each drow In ds.Tables(0).Rows
            drow("BasicSalary") = Decrypt(drow("BasicSalary"))
            drow("Allowance") = Decrypt(drow("Allowance"))
            drow("HouseRent") = Decrypt(drow("HouseRent"))
            drow("Conveyance") = Decrypt(drow("Conveyance"))
            drow("OverTimeAmount") = Decrypt(drow("OverTimeAmount"))
            drow("Deduction1") = Decrypt(drow("Deduction1"))
            drow("Deduction2") = Decrypt(drow("Deduction2"))
            drow("Deduction3") = Decrypt(drow("Deduction3"))
            drow("Deduction4") = Decrypt(drow("Deduction4"))
            drow("TotalInstallment") = CDec(drow("Deduction1")) + CDec(drow("Deduction2")) + CDec(drow("Deduction3")) + CDec(drow("Deduction4"))
            drow("TotalDeduction") = Decrypt(drow("TotalDeduction"))
            drow("ProrataDeduction") = Decrypt(drow("ProrataDeduction"))
            drow("Gross") = Decrypt(drow("Gross"))
            drow("Net") = Decrypt(drow("Net"))
        Next
        sqlCon.Close()
        ListEmployeePayrollForReport = ds
    End Function
    Public Function ListEmployeePaySlip(ByVal ThisCompanyID As Integer, ByVal MonthCode As Integer, ByVal YearCode As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        'If ThisCompanyID = 1 Then
        '    ThisCompanyID = 0
        'End If
        If ThisCompanyID = 0 Then
            strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                    "a.EmpAppointmentDate, " & _
                    "BasicSalary,Allowance,HouseRent,Conveyance,OverTimeAmount," & _
                    "Deduction1,Deduction2,Deduction3,Deduction4,ProrataDeduction,TotalDeduction,GrossAmount Gross, NetAmount Net," & _
                    "Remark1,Remark2,Remark3,DaysWorked,TotalDays,TotalDays-DaysWorked AbsentDays " & _
                    "from Employee a,EmployeePayroll b " & _
                    "where  a.EmpID=b.EmpID " & _
                    "and ltrim(rtrim(a.EmpStatus))='ACTIVE' " & _
                    "order by a.EmpName "
        Else
            strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                    "a.EmpAppointmentDate, " & _
                    "BasicSalary,Allowance,HouseRent,Conveyance,OverTimeAmount," & _
                    "Deduction1,Deduction2,Deduction3,Deduction4,ProrataDeduction,TotalDeduction,GrossAmount Gross, NetAmount Net," & _
                    "Remark1,Remark2,Remark3,DaysWorked,TotalDays,TotalDays-DaysWorked AbsentDays " & _
                    "from Employee a,EmployeePayroll b " & _
                    "where  a.EmpID=b.EmpID " & _
                    "and ltrim(rtrim(a.EmpStatus))='ACTIVE' and EmpCompany=" & ThisCompanyID & _
                    " order by a.EmpName "
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim drow As DataRow
        For Each drow In ds.Tables(0).Rows
            drow("BasicSalary") = Decrypt(drow("BasicSalary"))
            drow("Allowance") = Decrypt(drow("Allowance"))
            drow("HouseRent") = Decrypt(drow("HouseRent"))
            drow("Conveyance") = Decrypt(drow("Conveyance"))
            drow("OverTimeAmount") = Decrypt(drow("OverTimeAmount"))
            drow("Deduction1") = Decrypt(drow("Deduction1"))
            drow("Deduction2") = Decrypt(drow("Deduction2"))
            drow("Deduction3") = Decrypt(drow("Deduction3"))
            drow("Deduction4") = Decrypt(drow("Deduction4"))
            drow("TotalDeduction") = Decrypt(drow("TotalDeduction"))
            drow("ProrataDeduction") = Decrypt(drow("ProrataDeduction"))
            drow("Gross") = Decrypt(drow("Gross"))
            drow("Net") = Decrypt(drow("Net"))
        Next
        sqlCon.Close()
        ListEmployeePaySlip = ds
    End Function
    Public Function ListPayrollBankTransfer(ByVal ThisCompanyID As Integer, ByVal MonthCode As Integer, ByVal YearCode As Integer, ByVal BankID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If ThisCompanyID = 1 Then
            ThisCompanyID = 0
        End If
        If ThisCompanyID = 0 Then
            strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                    "a.EmpAppointmentDate, " & _
                    "BasicSalary,Allowance,HouseRent,Conveyance,OverTimeAmount," & _
                    "EmpBankAccount,EmpBankBranch," & _
                    "Deduction1,Deduction2,Deduction3,Deduction4,0.000 TotalInstallment,ProrataDeduction,TotalDeduction,GrossAmount Gross, NetAmount Net,DaysWorked,TotalDays,TotalDays-DaysWorked AbsentDays " & _
                    "from Employee a,EmployeePayroll b " & _
                    "where a.EmpBankID=" & BankID & "  and a.EmpID=b.EmpID " & _
                    "and ltrim(rtrim(a.EmpStatus))='ACTIVE' " & _
                    "order by a.EmpName "
        Else
            strsql = "select 0 SL,a.EmpID,a.EmpPFnumber,a.EmpType,a.EmpName," & _
                    "a.EmpAppointmentDate," & _
                    "BasicSalary,Allowance,HouseRent,Conveyance,OverTimeAmount," & _
                    "EmpBankAccount,EmpBankBranch," & _
                    "Deduction1,Deduction2,Deduction3,Deduction4,0.000 TotalInstallment,ProrataDeduction,TotalDeduction,GrossAmount Gross, NetAmount Net,DaysWorked,TotalDays,TotalDays-DaysWorked AbsentDays " & _
                    "from Employee a,EmployeePayroll b " & _
                    "where a.EmpBankID=" & BankID & " and a.EmpID=b.EmpID " & _
                    "and ltrim(rtrim(a.EmpStatus))='ACTIVE' and a.EmpCompany=" & ThisCompanyID & _
                    " order by a.EmpName "
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim drow As DataRow
        For Each drow In ds.Tables(0).Rows
            drow("BasicSalary") = Decrypt(drow("BasicSalary"))
            drow("Allowance") = Decrypt(drow("Allowance"))
            drow("HouseRent") = Decrypt(drow("HouseRent"))
            drow("Conveyance") = Decrypt(drow("Conveyance"))
            drow("OverTimeAmount") = Decrypt(drow("OverTimeAmount"))
            drow("Deduction1") = Decrypt(drow("Deduction1"))
            drow("Deduction2") = Decrypt(drow("Deduction2"))
            drow("Deduction3") = Decrypt(drow("Deduction3"))
            drow("Deduction4") = Decrypt(drow("Deduction4"))
            drow("TotalInstallment") = CDec(drow("Deduction1")) + CDec(drow("Deduction2")) + CDec(drow("Deduction3")) + CDec(drow("Deduction4"))
            drow("TotalDeduction") = Decrypt(drow("TotalDeduction"))
            drow("ProrataDeduction") = Decrypt(drow("ProrataDeduction"))
            drow("Gross") = Decrypt(drow("Gross"))
            drow("Net") = Decrypt(drow("Net"))
        Next
        sqlCon.Close()
        ListPayrollBankTransfer = ds
    End Function
    Public Function ListAllEmployeeType() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from EmployeeGroup " & _
                 "order by GroupName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        ListAllEmployeeType = ds
    End Function
    Public Sub DefaultAbsentDaysToZero()
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Update EmployeeBenefit set DaysWorked=0"
        sqlCon.Open()
        Dim sqlCMD As New SqlCommand(strsql, sqlCon)
        Try
            sqlCMD.ExecuteNonQuery()
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
    End Sub
    Public Function GetEmployeeBankDetails(ByVal EmpID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select EmpBankID,EmpBankBranch,EmpBankAccount from Employee " & _
                 "where EmpID=" & EmpID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetEmployeeBankDetails = ds
    End Function
    Public Function ListAllBank() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from EmployeeBank " & _
                 "order by BankName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        ListAllBank = ds
    End Function
    Public Function GetEmployeeDetails(ByVal EmpID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from Employee where EmpID=" & EmpID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetEmployeeDetails = ds
    End Function
    Public Function GetEmployees() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select EmpID,EmpName,EmpPFnumber from Employee where ltrim(rtrim(EmpStatus))='ACTIVE' order by EmpName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetEmployees = ds
    End Function
    Public Function GetBioDeviceIDS() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select id,device,location from tb_device order by id desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetBioDeviceIDS = ds
    End Function
    Public Function GetEmployeesByGroup(ByVal categ As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select EmpID,EmpName,EmpPFnumber from Employee where ltrim(rtrim(EmpStatus))='ACTIVE' and EmpCategory = " & categ & " order by EmpName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetEmployeesByGroup = ds
    End Function
    Public Function GetDatesbtwnDateRange(ByVal StartDate As String, ByVal EndDate As String) As DataSet

        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        'strsql = "SELECT DATEADD(day, z.num, " & StartDate & ") FROM ( SELECT b10.i + b9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i num FROM (SELECT 0 i UNION ALL SELECT 1) b0 CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1 CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2 CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3 CROSS JOIN (SELECT 0 i UNION ALL SELECT 16) b4 CROSS JOIN (SELECT 0 i UNION ALL SELECT 32) b5 CROSS JOIN (SELECT 0 i UNION ALL SELECT 64) b6 CROSS JOIN (SELECT 0 i UNION ALL SELECT 128) b7 CROSS JOIN (SELECT 0 i UNION ALL SELECT 256) b8 CROSS JOIN (SELECT 0 i UNION ALL SELECT 512) b9 CROSS JOIN (SELECT 0 i UNION ALL SELECT 1024) b10 )z WHERE z.num <= DATEDIFF(day, " & StartDate & ", " & EndDate & ")ORDER BY z.num"
        strsql = "SELECT DATEADD(day, z.num, '" & StartDate & "') FROM ( SELECT b10.i + b9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i num FROM (SELECT 0 i UNION ALL SELECT 1) b0 CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1 CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2 CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3 CROSS JOIN (SELECT 0 i UNION ALL SELECT 16) b4 CROSS JOIN (SELECT 0 i UNION ALL SELECT 32) b5 CROSS JOIN (SELECT 0 i UNION ALL SELECT 64) b6 CROSS JOIN (SELECT 0 i UNION ALL SELECT 128) b7 CROSS JOIN (SELECT 0 i UNION ALL SELECT 256) b8 CROSS JOIN (SELECT 0 i UNION ALL SELECT 512) b9 CROSS JOIN (SELECT 0 i UNION ALL SELECT 1024) b10 )z WHERE z.num <= DATEDIFF(day,'" & StartDate & "','" & EndDate & "')ORDER BY z.num"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet
        Try
            da.Fill(ds)
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetDatesbtwnDateRange = ds
    End Function
    Public Function GetEmployeeWeeklyOff(ByVal EmpID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from EmployeeWeeklyOff where EmpID=" & EmpID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetEmployeeWeeklyOff = ds
    End Function
    Public Function GetEmployeeChildren(ByVal EmpID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select AdmissionNo from EmployeeStudent a,StudentProfile b " & _
                "where a.StudentID=b.StudentID and EmpID=" & EmpID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetEmployeeChildren = ds
    End Function
    Public Function GetEmployeeBenefit(ByVal EmpID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.EmpPFnumber,a.EmpName,b.* from Employee a,EmployeeBenefit b " & _
                "where a.EmpID=b.EmpID and a.EmpID=" & EmpID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try

        Dim drow As DataRow
        For Each drow In ds.Tables(0).Rows
            drow("BasicSalary") = Decrypt(drow("BasicSalary"))
            drow("Allowance") = Decrypt(drow("Allowance"))
            drow("HouseRent") = Decrypt(drow("HouseRent"))
            drow("Conveyance") = Decrypt(drow("Conveyance"))
            drow("OverTimeAmount") = Decrypt(drow("OverTimeAmount"))
            drow("Loan1Amount") = Decrypt(drow("Loan1Amount"))
            drow("Loan2Amount") = Decrypt(drow("Loan2Amount"))
            drow("Loan3Amount") = Decrypt(drow("Loan3Amount"))
            drow("Loan4Amount") = Decrypt(drow("Loan4Amount"))
            drow("Loan1Installment") = Decrypt(drow("Loan1Installment"))
            drow("Loan2Installment") = Decrypt(drow("Loan2Installment"))
            drow("Loan3Installment") = Decrypt(drow("Loan3Installment"))
            drow("Loan4Installment") = Decrypt(drow("Loan4Installment"))
            drow("AirTicketFare") = Decrypt(drow("AirTicketFare"))
        Next
        sqlCon.Close()
        GetEmployeeBenefit = ds
    End Function
    Public Function GetEmployeeOvertimeData(ByVal EmpID As Integer, ByVal MonthCode As String, ByVal YearCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from EmployeeOvertime " & _
                "where EmpID=" & EmpID & " and MonthCode='" & MonthCode & "' and YearCode='" & YearCode & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetEmployeeOvertimeData = ds
    End Function
    Public Function ProcessOvertimeData(ByVal InvStatus As String, ByVal InvXml As String) As String
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(InvXml)
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim OtMonth As String = RootNode.SelectSingleNode("InvoiceHead/OtMonth").InnerText
        Dim OtYear As String = RootNode.SelectSingleNode("InvoiceHead/OtYear").InnerText
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim sqlCon2 As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlCmd As New SqlCommand()
        Dim sqlCmd2 As New SqlCommand()
        sqlCmd.Connection = sqlCon
        sqlCon.Open()
        sqlCmd2.Connection = sqlCon2
        sqlCon2.Open()
        Dim sqlTrans As SqlTransaction
        sqlTrans = sqlCon.BeginTransaction
        sqlCmd.Transaction = sqlTrans
        strsql = "Delete from EmployeeOvertime where MonthCode=" & OtMonth & " and YearCode='" & OtYear & "'"
        sqlCmd.CommandText = strsql
        sqlCmd.ExecuteNonQuery()
        If InvStatus = "POST" Then
            strsql = "Update EmployeeBenefit set OvertimeAmount=0"
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
        End If

        Dim i As Integer
        Dim TotCost As Double = 0
        For i = 0 To nodeList.Count - 1
            Dim EmpID As Integer = nodeList.Item(i).ChildNodes(0).InnerText
            Dim EmpPfNo As String = nodeList.Item(i).ChildNodes(1).InnerText
            EmpID = GetEmployeeID(EmpPfNo)
            Dim NormalDay As String = PrefixInteger(nodeList.Item(i).ChildNodes(2).InnerText)
            Dim WeeklyOff As String = PrefixInteger(nodeList.Item(i).ChildNodes(3).InnerText)
            Dim Holiday As String = PrefixInteger(nodeList.Item(i).ChildNodes(4).InnerText)
            Dim CompOff As String = PrefixInteger(nodeList.Item(i).ChildNodes(5).InnerText)
            strsql = "Insert into EmployeeOverTime (EmpID,MonthCode,YearCode,NormalDay,CompOff,Holiday,WeeklyOff) " & _
                "values(" & EmpID & "," & OtMonth & ",'" & OtYear & "'," & NormalDay & "," & CompOff & "," & Holiday & "," & WeeklyOff & ")"
            sqlCmd.CommandText = strsql
            Try
                sqlCmd.ExecuteNonQuery()
            Catch ex3 As SqlException
                sqlTrans.Rollback()
                Return "ERROR"
            End Try
            If InvStatus = "POST" Then
                Dim Namt, Hamt, Famt, Wamt, TotOT As Decimal
                Dim BasicSalary As Decimal = 0.0
                Namt = 0.0
                Hamt = 0.0
                Famt = 0.0
                Wamt = 0.0
                TotOT = 0.0
                BasicSalary = GetEmployeeSalary(EmpID)
                '26/8=208
                If NormalDay <> 0 Then
                    Namt = (BasicSalary / 208) * 1.25 * NormalDay
                End If
                If WeeklyOff <> 0 Then
                    Wamt = (BasicSalary / 208) * 1.5 * WeeklyOff
                End If
                'If Friday <> 0 Then ' to be treated as WeeklyOff
                '    Famt = (BasicSalary / 208) * 1.5 * Friday
                'End If
                If Holiday <> 0 Then
                    Hamt = (BasicSalary / 208) * 1 * Holiday
                End If
                TotOT = Namt + Wamt + Famt + Hamt
                TotOT = TotOT.Round(TotOT, 2)
                strsql = "Update EmployeeBenefit set OvertimeAmount='" & Encrypt(TotOT) & "' where EmpID=" & EmpID
                sqlCmd.CommandText = strsql
                sqlCmd.ExecuteNonQuery()
            End If
        Next
        sqlTrans.Commit()
        sqlCon.Close()
        Return "OK"
    End Function
    Public Function GetOvertimeData(ByVal MonthCode As String, ByVal YearCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.EmpID,a.EmpPfNumber,a.EmpName,b.NormalDay,b.CompOff,b.Holiday,b.WeeklyOff " & _
                "from Employee a,EmployeeOvertime b,EmployeeBenefit c " & _
                "where c.EligibleForOvertime=1 and a.EmpID=c.EmpID and a.EmpID=b.EmpID and MonthCode='" & MonthCode & "' and YearCode='" & YearCode & "' " & _
                "order by EmpPfNumber"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("InvoiceLines")
        Try
            da.Fill(ds, "InvoiceLines")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetOvertimeData = ds
    End Function
    Public Function GetOvertimeDataFromAttendance(ByVal FromDate As String, ByVal ToDate As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        FromDate = Right(FromDate, 4) & "/" & FromDate.Substring(3, 2) & "/" & Left(FromDate, 2)
        ToDate = Right(ToDate, 4) & "/" & ToDate.Substring(3, 2) & "/" & Left(ToDate, 2)

        strsql = "select a.EmpID,a.EmpPfNumber,a.EmpName," & _
                "sum(CASE b.EmpDayType WHEN 1 THEN b.EmpOtHours ELSE 0.00 END) AS NormalDay," & _
                "sum(CASE b.EmpDayType WHEN 2 THEN b.EmpOtHours ELSE 0.00 END) AS WeeklyOff," & _
                "sum(CASE b.EmpDayType WHEN 3 THEN b.EmpOtHours ELSE 0.00 END) AS HoliDay," & _
                "0.000 CompOff from Employee a,EmployeeAttendance b,EmployeeBenefit c " & _
                "where c.EligibleForOvertime=1 and a.EmpID=c.EmpID and a.EmpID=b.EmpID " & _
                "and EmpDate between '" & FromDate & "' and '" & ToDate & "' " & _
                "Group By a.EmpID,a.EmpPfNumber,a.EmpName order by a.EmpPfNumber"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("InvoiceLines")
        Try
            da.Fill(ds, "InvoiceLines")
            Dim drow As DataRow
            For Each drow In ds.Tables(0).Rows
                If CInt(drow("NormalDay")) < 0.5 Then
                    drow("NormalDay") = "0.00"
                End If
                If CInt(drow("HoliDay")) < 0.5 Then
                    drow("HoliDay") = "0.00"
                End If
                If CInt(drow("WeeklyOff")) < 0.5 Then
                    drow("WeeklyOff") = "0.00"
                End If
            Next
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetOvertimeDataFromAttendance = ds
    End Function
    Public Function GetVacationTakenDays(ByVal EmpID As Integer, ByVal StartDate As String, ByVal EndDate As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select EmpAppointmentDate,sum(WithDays) WithDays,sum(WithoutDays) WithoutDays " & _
                "from (Select a.EmpID,sum(VacWSdays) WithDays,0 WithoutDays " & _
                "from EmployeeVacation a,Employee b " & _
                "where a.EmpID = b.EmpID And a.EmpID = " & EmpID & _
                "and VacationTo<'" & EndDate & "' and VacationTo>EmpAppointmentDate " & _
                "group by EmpAppointmentDate " & _
                "union all " & _
                "Select a.EmpID,0 WithDays,sum(VacWOdays) WithoutDays " & _
                "from EmployeeVacation a,Employee b " & _
                "where a.EmpID=b.EmpID and a.EmpID=" & EmpID & _
                "and VacationTo<'" & EndDate & "' and VacationTo>EmpAppointmentDate " & _
                "group by EmpAppointmentDate)x group by EmpAppointmentDate"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetVacationTakenDays = ds
    End Function
    Public Function GetVacationTakenDaysByType(ByVal EmpID As Integer, ByVal StartDate As String, ByVal EndDate As String, ByVal VacType As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        StartDate = ValidDate(StartDate)
        EndDate = ValidDate(EndDate)
        If VacType <> "I" Then
            strsql = "select EmpAppointmentDate,sum(WithDays) WithDays,sum(WithoutDays) WithoutDays " & _
                    "from (Select EmpAppointmentDate,sum(VacWSdays) WithDays,0 WithoutDays " & _
                    "from EmployeeVacation a,Employee b " & _
                    "where a.EmpID = b.EmpID And a.EmpID = " & EmpID & _
                    "and VacationTo<'" & EndDate & "' " & _
                    "and VacationType='" & VacType & "' group by EmpAppointmentDate " & _
                    "union all " & _
                    "Select EmpAppointmentDate,0 WithDays,sum(VacWOdays) WithoutDays " & _
                    "from EmployeeVacation a,Employee b " & _
                    "where a.EmpID=b.EmpID and a.EmpID=" & EmpID & _
                    "and VacationTo<'" & EndDate & "'  " & _
                    "and VacationType='" & VacType & "' group by EmpAppointmentDate)x group by EmpAppointmentDate"
        Else
            strsql = "select EmpAppointmentDate,sum(WithDays) WithDays,sum(WithoutDays) WithoutDays " & _
                    "from (Select EmpAppointmentDate,sum(VacWSdays) WithDays,0 WithoutDays " & _
                    "from EmployeeVacation a,Employee b " & _
                    "where a.EmpID = b.EmpID And a.EmpID = " & EmpID & _
                    "and VacationTo<'" & EndDate & "' " & _
                    "group by EmpAppointmentDate " & _
                    "union all " & _
                    "Select EmpAppointmentDate,0 WithDays,sum(VacWOdays) WithoutDays " & _
                    "from EmployeeVacation a,Employee b " & _
                    "where a.EmpID=b.EmpID and a.EmpID=" & EmpID & _
                    "and VacationTo<'" & EndDate & "' " & _
                    "group by EmpAppointmentDate)x group by EmpAppointmentDate"
        End If
        'and VacationTo>EmpAppointmentDate " & _
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetVacationTakenDaysByType = ds
    End Function
    Public Function ListVacationProvision(ByVal ThisCompany As Integer, ByVal CalcDate As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If ThisCompany = 1 Then
            ThisCompany = 0
        End If
        CalcDate = ValidDate(CalcDate)
        sqlCon.Open()
        '"sum(WithDays) WithDays,sum(WithoutDays) WithoutDays,max(Salary) Salary,max(AnnualVacationDays) VacDays," & _
        If ThisCompany > 0 Then
            strsql = "Select EmpID,EmpPFnumber,EmpName,EmpAppointmentDate," & _
                "sum(WithDays) WithDays,sum(WithoutDays) WithoutDays,0.000 Salary,max(AnnualVacationDays) VacDays," & _
                "0 NetServiceDay,0 FirstFiveYear,0 OverFiveYear,0 OverTenYear,0 TotDue,0 NetDays,0.000 TotalProvn,0.000 MonthProvn " & _
                "from (Select b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate," & _
                "sum(VacWSdays) WithDays,0 WithoutDays,max(AnnualVacationDays) AnnualVacationDays " & _
                "from Employee b, EmployeeVacation a,EmployeeBenefit c " & _
                "where b.EmpCompany=" & ThisCompany & " and a.EmpID = b.EmpID and a.EmpID=c.EmpID and '" & CalcDate & "'>=VacationFrom and VacationType='N' and b.EmpStatus='ACTIVE' " & _
                "group by b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate " & _
                "union all " & _
                "Select b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate," & _
                "0 WithDays,sum(VacWOdays) WithoutDays,max(AnnualVacationDays) AnnualVacationDays " & _
                "from Employee b, EmployeeVacation a,EmployeeBenefit c " & _
                "where b.EmpCompany=" & ThisCompany & " and a.EmpID = b.EmpID and a.EmpID=c.EmpID and '" & CalcDate & "'>=VacationFrom  and b.EmpStatus='ACTIVE' " & _
                "group by b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate)x " & _
                "group by EmpID,EmpPFnumber,EmpName,EmpAppointmentDate " & _
                "union all " & _
                "Select b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate,0 WithDays,0 WithoutDays,0.000 Salary,AnnualVacationDays VacDays, " & _
                "0 NetServiceDay,0 FirstFiveYear,0 OverFiveYear,0 OverTenYear,0 TotDue,0 NetDays,0.000 TotalProvn,0.000 MonthProvn " & _
                "from Employee b,EmployeeBenefit c where b.EmpCompany=" & ThisCompany & " and b.EmpStatus='ACTIVE' and b.EmpID=c.EmpID  " & _
                "and b.EmpID not in (" & _
                "Select b.EmpID from Employee b,EmployeeVacation a " & _
                "where a.EmpID = b.EmpID and '" & CalcDate & "'>=VacationFrom and VacationType='N' and b.EmpStatus='ACTIVE') " & _
                "order by EmpPFnumber "
        Else
            strsql = "Select EmpID,EmpPFnumber,EmpName,EmpAppointmentDate," & _
                "sum(WithDays) WithDays,sum(WithoutDays) WithoutDays,0.000 Salary,max(AnnualVacationDays) VacDays," & _
                "0 NetServiceDay,0 FirstFiveYear,0 OverFiveYear,0 OverTenYear,0 TotDue,0 NetDays,0.000 TotalProvn,0.000 MonthProvn " & _
                "from (Select b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate," & _
                "sum(VacWSdays) WithDays,0 WithoutDays,max(AnnualVacationDays) AnnualVacationDays " & _
                "from Employee b, EmployeeVacation a,EmployeeBenefit c " & _
                "where a.EmpID = b.EmpID and a.EmpID=c.EmpID and '" & CalcDate & "'>=VacationFrom and VacationType='N' and b.EmpStatus='ACTIVE' " & _
                "group by b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate " & _
                "union all " & _
                "Select b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate," & _
                "0 WithDays,sum(VacWOdays) WithoutDays,max(AnnualVacationDays) AnnualVacationDays " & _
                "from Employee b, EmployeeVacation a,EmployeeBenefit c " & _
                "where a.EmpID = b.EmpID and a.EmpID=c.EmpID and '" & CalcDate & "'>=VacationFrom  and b.EmpStatus='ACTIVE' " & _
                "group by b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate)x " & _
                "group by EmpID,EmpPFnumber,EmpName,EmpAppointmentDate " & _
                "union all " & _
                "Select b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate,0 WithDays,0 WithoutDays,0.000 Salary,AnnualVacationDays VacDays, " & _
                "0 NetServiceDay,0 FirstFiveYear,0 OverFiveYear,0 OverTenYear,0 TotDue,0 NetDays,0.000 TotalProvn,0.000 MonthProvn " & _
                "from Employee b,EmployeeBenefit c where b.EmpStatus='ACTIVE' and b.EmpID=c.EmpID " & _
                "and b.EmpID not in (" & _
                "Select b.EmpID from Employee b,EmployeeVacation a " & _
                "where a.EmpID = b.EmpID and '" & CalcDate & "'>=VacationFrom and VacationType='N' and b.EmpStatus='ACTIVE') " & _
                "order by EmpPFnumber "
        End If
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
            Dim drow As DataRow
            Dim gtProvn, gmProvn As Decimal
            CalcDate = ValidDate(CalcDate)
            Dim CalDate As Date = CalcDate
            For Each drow In ds.Tables(0).Rows
                Dim EmpID As Integer = 0
                Dim Special As Boolean = False
                Dim SplDays As Integer = 0
                If Not IsDBNull(drow("VacDays")) Then
                    If CInt(drow("VacDays")) > 0 Then
                        Special = True
                        SplDays = CInt(drow("VacDays"))
                    End If
                End If

                Dim bSalary As Decimal = 0.0
                bSalary = GetEmployeeSalary(EmpID)
                drow("Salary") = bSalary

                Dim TotDay As Integer = 0
                Dim Speriod As Integer = 0
                Dim Adate As String
                Dim Syears As Decimal = 0.0
                Adate = drow("EmpAppointmentDate")
                Speriod = DateDiff(DateInterval.Day, CDate(Adate), CalDate)
                Speriod = Speriod - (CInt(drow("WithoutDays")))
                Syears = Speriod / 365
                Dim Fday As Integer = 0
                Dim Sday As Integer = 0
                Dim Tday As Integer = 0
                Select Case Speriod
                    Case Is < (5 * 365)
                        If Special = False Then
                            Fday = Speriod * (14 / 365)
                        Else
                            Fday = Speriod * (SplDays / 365)
                        End If
                    Case Is >= (5 * 365)
                        If Speriod < 10 * 365 Then
                            If Special = False Then
                                Fday = 14 * 5
                                Sday = (Speriod - 1825) * (21 / 365)
                            Else
                                Fday = SplDays * 5
                                Sday = (Speriod - 1825) * (SplDays / 365)
                            End If
                        Else
                            If Special = False Then
                                Fday = 14 * 5
                                Sday = 21 * 5
                                Tday = (Speriod - 3650) * (30 / 365)
                            Else
                                Fday = SplDays * 5
                                Sday = SplDays * 5
                                Tday = (Speriod - 3650) * (SplDays / 365)
                            End If
                        End If
                End Select
                Dim ElgDays As Integer
                ElgDays = Fday + Sday + Tday
                ElgDays = ElgDays - (CInt(drow("WithDays")))
                Dim Provn As Decimal = 0.0
                Provn = ElgDays * (drow("Salary") / 30)
                Dim MonthProvn As Decimal = 0.0
                If SplDays > 0 Then
                    MonthProvn = ((drow("Salary") / 30) * SplDays) / 12
                Else
                    If Speriod < (5 * 365) Then
                        MonthProvn = ((drow("Salary") / 30) * 14) / 12
                    Else
                        MonthProvn = ((drow("Salary") / 30) * 21) / 12
                    End If
                End If
                'MonthProvn = Provn / (Speriod / 30)
                drow("NetServiceDay") = Speriod
                drow("FirstFiveYear") = Fday
                drow("OverFiveYear") = Sday
                drow("OverTenYear") = Tday
                drow("TotDue") = Fday + Sday + Tday
                drow("NetDays") = ElgDays
                drow("TotalProvn") = Provn.ToString("#0.000")
                drow("MonthProvn") = MonthProvn.ToString("#0.000")
                gtProvn += Provn
                gmProvn += MonthProvn
            Next
            Dim dRow2 As DataRow
            dRow2 = ds.Tables(0).NewRow()
            dRow2("EmpName") = "Grand Total:"
            dRow2("TotalProvn") = gtProvn.ToString("####.000")
            dRow2("MonthProvn") = gmProvn.ToString("####.000")
            ds.Tables(0).Rows.Add(dRow2)
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        ListVacationProvision = ds
    End Function
    Public Function ListIndemnityProvision(ByVal ThisCompany As Integer, ByVal CalcDate As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If ThisCompany = 1 Then
            ThisCompany = 0
        End If
        CalcDate = ValidDate(CalcDate)
        'c.BasicSalary+c.HouseRent+c.Conveyance Salary
        sqlCon.Open()
        If ThisCompany > 0 Then
            strsql = "Select EmpID,EmpPFnumber,EmpName,EmpAppointmentDate," & _
                "sum(WithDays) WithDays,sum(WithoutDays) WithoutDays,0.000 Salary,max(AnnualVacationDays) VacDays," & _
                "0 NetServiceDay,0 FirstFiveYear,0 OverFiveYear,0 OverTenYear,0 TotDue,0 NetDays,0.000 TotalProvn,0.000 MonthProvn " & _
                "from (Select b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate," & _
                "sum(VacWSdays) WithDays,0 WithoutDays,max(AnnualVacationDays) AnnualVacationDays " & _
                "from Employee b, EmployeeVacation a,EmployeeBenefit c " & _
                "where b.EmpCompany=" & ThisCompany & " and a.EmpID = b.EmpID and a.EmpID=c.EmpID and '" & CalcDate & "'>=VacationFrom and VacationType='N' and b.EmpStatus='ACTIVE' " & _
                "group by b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate " & _
                "union all " & _
                "Select b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate," & _
                "0 WithDays,sum(VacWOdays) WithoutDays,max(AnnualVacationDays) AnnualVacationDays " & _
                "from Employee b, EmployeeVacation a,EmployeeBenefit c " & _
                "where b.EmpCompany=" & ThisCompany & " and a.EmpID = b.EmpID and a.EmpID=c.EmpID and '" & CalcDate & "'>=VacationFrom  and b.EmpStatus='ACTIVE' " & _
                "group by b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate)x " & _
                "group by EmpID,EmpPFnumber,EmpName,EmpAppointmentDate " & _
                "union all " & _
                "Select b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate,0 WithDays,0 WithoutDays,0.000 Salary,AnnualVacationDays VacDays, " & _
                "0 NetServiceDay,0 FirstFiveYear,0 OverFiveYear,0 OverTenYear,0 TotDue,0 NetDays,0.000 TotalProvn,0.000 MonthProvn " & _
                "from Employee b,EmployeeBenefit c where b.EmpCompany=" & ThisCompany & " and b.EmpStatus='ACTIVE' and b.EmpID=c.EmpID  " & _
                "and b.EmpID not in (" & _
                "Select b.EmpID from Employee b,EmployeeVacation a " & _
                "where a.EmpID = b.EmpID and '" & CalcDate & "'>=VacationFrom and VacationType='N' and b.EmpStatus='ACTIVE') " & _
                "order by EmpPFnumber "
        Else
            strsql = "Select EmpID,EmpPFnumber,EmpName,EmpAppointmentDate," & _
                "sum(WithDays) WithDays,sum(WithoutDays) WithoutDays,0.000 Salary,max(AnnualVacationDays) VacDays," & _
                "0 NetServiceDay,0 FirstFiveYear,0 OverFiveYear,0 OverTenYear,0 TotDue,0 NetDays,0.000 TotalProvn,0.000 MonthProvn " & _
                "from (Select b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate," & _
                "sum(VacWSdays) WithDays,0 WithoutDays,max(AnnualVacationDays) AnnualVacationDays " & _
                "from Employee b, EmployeeVacation a,EmployeeBenefit c " & _
                "where a.EmpID = b.EmpID and a.EmpID=c.EmpID and '" & CalcDate & "'>=VacationFrom and VacationType='N' and b.EmpStatus='ACTIVE' " & _
                "group by b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate " & _
                "union all " & _
                "Select b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate," & _
                "0 WithDays,sum(VacWOdays) WithoutDays,max(AnnualVacationDays) AnnualVacationDays " & _
                "from Employee b, EmployeeVacation a,EmployeeBenefit c " & _
                "where a.EmpID = b.EmpID and a.EmpID=c.EmpID and '" & CalcDate & "'>=VacationFrom  and b.EmpStatus='ACTIVE' " & _
                "group by b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate)x " & _
                "group by EmpID,EmpPFnumber,EmpName,EmpAppointmentDate " & _
                "union all " & _
                "Select b.EmpID,b.EmpPFnumber,b.EmpName,EmpAppointmentDate,0 WithDays,0 WithoutDays,0.000 Salary,AnnualVacationDays VacDays, " & _
                "0 NetServiceDay,0 FirstFiveYear,0 OverFiveYear,0 OverTenYear,0 TotDue,0 NetDays,0.000 TotalProvn,0.000 MonthProvn " & _
                "from Employee b,EmployeeBenefit c where b.EmpStatus='ACTIVE' and b.EmpID=c.EmpID " & _
                "and b.EmpID not in (" & _
                "Select b.EmpID from Employee b,EmployeeVacation a " & _
                "where a.EmpID = b.EmpID and '" & CalcDate & "'>=VacationFrom and VacationType='N' and b.EmpStatus='ACTIVE') " & _
                "order by EmpPFnumber "
        End If
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
            Dim drow As DataRow
            Dim gtProvn, gmProvn As Decimal
            CalcDate = ValidDate(CalcDate)
            Dim CalDate As Date = CalcDate
            For Each drow In ds.Tables(0).Rows
                Dim EmpID As Integer = 0
                EmpID = drow("EmpID")

                Dim Special As Boolean = False
                Dim SplDays As Integer = 0
                If Not IsDBNull(drow("VacDays")) Then
                    If CInt(drow("VacDays")) > 0 Then
                        Special = True
                        SplDays = CInt(drow("VacDays"))
                    End If
                End If
                Dim TotDay As Integer = 0
                Dim Speriod As Integer = 0
                Dim Adate As String
                Dim Syears As Decimal = 0.0

                Dim iSalary As Decimal = 0.0
                iSalary = GetEmployeeSalaryForIndemnity(EmpID)
                drow("Salary") = iSalary

                Adate = drow("EmpAppointmentDate")
                Speriod = DateDiff(DateInterval.Day, CDate(Adate), CalDate)
                Speriod = Speriod - (CInt(drow("WithoutDays")))
                Syears = Speriod / 365
                Dim Fday As Integer = 0
                Dim Sday As Integer = 0
                Dim Tday As Integer = 0
                Select Case Speriod
                    Case Is < (5 * 365)
                        Fday = Speriod * (15 / 365)
                    Case Is >= (5 * 365)
                        If Speriod < 10 * 365 Then
                            Fday = 15 * 5
                            Sday = (Speriod - 1826) * (30 / 365)
                        Else
                            Fday = 15 * 5
                            Sday = 30 * 5
                            Tday = (Speriod - 3650) * (30 / 365)
                        End If
                End Select
                Dim ElgDays As Integer
                ElgDays = Fday + Sday + Tday
                Dim Provn As Decimal = 0.0
                If Speriod < (5 * 365) Then
                    Provn = (drow("Salary") / 26) * Fday
                Else
                    Provn = (drow("Salary") / 26) * Fday
                    Provn = Provn + (drow("Salary") / 365) * (Speriod - 1826)
                End If

                Dim MonthProvn As Decimal = 0.0
                MonthProvn = (drow("Salary") / 12)
                drow("NetServiceDay") = Speriod
                drow("FirstFiveYear") = Fday
                drow("OverFiveYear") = Sday
                drow("OverTenYear") = Tday
                drow("TotDue") = Fday + Sday + Tday
                drow("NetDays") = ElgDays
                drow("TotalProvn") = Provn.ToString("#0.000")
                drow("MonthProvn") = MonthProvn.ToString("#0.000")
                gtProvn += Provn
                gmProvn += MonthProvn
            Next
            Dim dRow2 As DataRow
            dRow2 = ds.Tables(0).NewRow()
            dRow2("EmpName") = "Grand Total:"
            dRow2("TotalProvn") = gtProvn.ToString("####.000")
            dRow2("MonthProvn") = gmProvn.ToString("####.000")
            ds.Tables(0).Rows.Add(dRow2)
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        ListIndemnityProvision = ds
    End Function
    Public Function GetNextEmployeeID(ByVal TypeID As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select max(cast(isnull(EmpPfNumber,0) as int))+1 NextEmpID from Employee "
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim NextInv As String
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            If Not IsDBNull(rdr("NextEmpID")) Then
                NextInv = CStr(rdr("NextEmpID"))
            Else
                NextInv = 1
            End If
            Dim nlen As Integer
            nlen = NextInv.Trim.Length
            Dim lzero As String = "00000000"
            If nlen < 4 Then
                NextInv = lzero.Substring(1, 4 - nlen) & NextInv
            End If
        End While
        sqlCon.Close()
        Return NextInv
    End Function
    Public Function GetEmployeeNameFromPfNo(ByVal PFnumber As String) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select EmpName from Employee " & _
                "where EmpPFnumber='" & PFnumber & "'"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)

        Dim NextInv As String = ""
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            NextInv = rdr("EmpName")
        End While
        sqlCon.Close()
        Return NextInv
    End Function
    Public Function GetEmployeeSalary(ByVal EmpID As Integer) As Decimal
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select isnull(BasicSalary,0) BasicSalary from EmployeeBenefit with (NoLock) " & _
                "where EmpID=" & EmpID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim NextInv As Decimal = 0.0
        Dim bSalary As String = "0"
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            bSalary = PrefixInteger(Decrypt(rdr("BasicSalary")))
        End While
        NextInv = Decimal.Parse(bSalary)
        sqlCon.Close()
        Return NextInv
    End Function
    Public Function GetEmployeeSalaryForIndemnity(ByVal EmpID As Integer) As Decimal
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        'strsql = "Select isnull(BasicSalary,0)+isnull(HouseRent,0)+isnull(Conveyance,0) BasicSalary from EmployeeBenefit with (NoLock) " & _
        '        "where EmpID=" & EmpID
        strsql = "Select BasicSalary,HouseRent,Conveyance from EmployeeBenefit with (NoLock) " & _
                "where EmpID=" & EmpID

        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim NextInv As Decimal = 0.0
        Dim bSal As String = "0"
        Dim Hra As String = "0"
        Dim Con As String = "0"
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            bSal = PrefixInteger(Decrypt(rdr("BasicSalary")))
            Hra = PrefixInteger(Decrypt(rdr("HouseRent")))
            Con = PrefixInteger(Decrypt(rdr("Conveyance")))
        End While
        NextInv = Decimal.Parse(bSal) + Decimal.Parse(Hra) + Decimal.Parse(Con)
        sqlCon.Close()
        Return NextInv
    End Function
    Public Function GetEmployeeAppointmentDate(ByVal EmpID As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select isnull(EmpAppointmentDate,'') EmpAppointmentDate from Employee " & _
                "where EmpID=" & EmpID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim NextInv As String
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            NextInv = rdr("EmpAppointmentDate")
        End While
        sqlCon.Close()
        Return NextInv
    End Function
    Public Function GetEmployeeID(ByVal PFnumber As String) As Integer
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select EmpID from Employee " & _
                "where EmpPFnumber='" & PFnumber & "'"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim NextInv As Integer
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            NextInv = rdr("EmpID")
        End While
        sqlCon.Close()
        Return NextInv
    End Function
    Public Function GetEmployeePFnumber(ByVal EmpID As String) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select isnull(EmpPfNumber,'') EmpPfNumber from Employee " & _
                "where EmpID=" & EmpID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim NextInv As String
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            NextInv = rdr("EmpPfNumber")
        End While
        sqlCon.Close()
        Return NextInv
    End Function
    Public Function GetDeductedAmounts(ByVal EmpID As String, ByVal PayMonth As Integer, ByVal PayYear As Integer, _
                ByRef Inst1 As Decimal, ByRef Inst2 As Decimal, ByRef Inst3 As Decimal, ByRef Inst4 As Decimal) As Integer
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select PayrollID,Deduction1 Ded1,Deduction2 Ded2,Deduction3 Ded3,Deduction4 Ded4 " & _
                "from EmployeePayroll with (NoLock) where EmpID=" & EmpID & " and MonthCode=" & PayMonth & " and YearCode=" & PayYear
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim PayID As Integer
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            PayID = rdr("PayrollID")
            Inst1 = Decimal.Parse(PrefixInteger(Decrypt(rdr("Ded1"))))
            Inst2 = Decimal.Parse(PrefixInteger(Decrypt(rdr("Ded2"))))
            Inst3 = Decimal.Parse(PrefixInteger(Decrypt(rdr("Ded3"))))
            Inst4 = Decimal.Parse(PrefixInteger(Decrypt(rdr("Ded4"))))
        End While
        sqlCon.Close()
        Return PayID
    End Function
    Public Sub GetLoanAmounts(ByVal EmpID As String, ByRef Cap1 As Decimal, ByRef Cap2 As Decimal, ByRef Cap3 As Decimal, ByRef Cap4 As Decimal)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select Loan1Amount Cap1,Loan1Amount Cap2,Loan1Amount Cap3,Loan1Amount Cap4 " & _
                "from EmployeeBenefit with (NoLock) where EmpID=" & EmpID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            Cap1 = Decimal.Parse(PrefixInteger(Decrypt(rdr("Cap1"))))
            Cap2 = Decimal.Parse(PrefixInteger(Decrypt(rdr("Cap2"))))
            Cap3 = Decimal.Parse(PrefixInteger(Decrypt(rdr("Cap3"))))
            Cap4 = Decimal.Parse(PrefixInteger(Decrypt(rdr("Cap4"))))
        End While
        sqlCon.Close()
    End Sub
    Public Function IsBenefitExist(ByVal PFnumber As String) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select isnull(count(*),0) mCount from EmployeeBenefit a,Employee b " & _
                "where a.EmpID = b.EmpID and EmpPFnumber='" & PFnumber & "'"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)

        Dim NextInv As Integer = 0
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            NextInv = rdr("mCount")
        End While
        sqlCon.Close()
        If NextInv = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
    Public Function IsEligibleForOvertime(ByVal PFnumber As String) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select isnull(count(*),0) mCount from EmployeeBenefit a,Employee b " & _
                "where a.EmpID = b.EmpID and EmpPFnumber='" & PFnumber & "' and a.EligibleForOvertime=1"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)

        Dim NextInv As Integer = 0
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            NextInv = rdr("mCount")
        End While
        sqlCon.Close()
        If NextInv = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
    Public Function IsPayrollProcessed(ByVal MonthCode As Integer, ByVal YearCode As Integer, ByVal ThisCompanyID As Integer) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select isnull(count(*),0) mCount from EmployeePayroll " & _
                "where YearCode='" & YearCode & "' and MonthCode='" & MonthCode & "' and CompanyID=" & ThisCompanyID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)

        Dim NextInv As Integer = 0
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            NextInv = rdr("mCount")
        End While
        sqlCon.Close()
        If NextInv = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
    Public Function AddNewEmployeeProfile(ByVal UserID As Integer, ByVal ProfileXml As String) As String
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(ProfileXml)
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim RegNo As Integer = RootNode.SelectSingleNode("Student/StRegNo").InnerText
        Dim StAdmit As String = ValidDate(RootNode.SelectSingleNode("Student/StAdmit").InnerText)
        Dim StName As String = RootNode.SelectSingleNode("Student/StName").InnerText
        Dim StPost As String = RootNode.SelectSingleNode("Student/StPost").InnerText
        Dim StCid As String = RootNode.SelectSingleNode("Student/StCid").InnerText
        Dim StCidExpiry As String = ValidDate(RootNode.SelectSingleNode("Student/StCidExpiry").InnerText)
        Dim StDob As String = ValidDate(RootNode.SelectSingleNode("Student/StDob").InnerText)
        Dim StPnum As String = RootNode.SelectSingleNode("Student/StPnum").InnerText
        Dim StPexpiry As String = ValidDate(RootNode.SelectSingleNode("Student/StPexpiry").InnerText)
        Dim StNationality As String = RootNode.SelectSingleNode("Student/StNationality").InnerText
        Dim StSex As String = RootNode.SelectSingleNode("Student/StSex").InnerText
        Dim StPhotoPath As String = RootNode.SelectSingleNode("Student/StPhotoPath").InnerText
        Dim StGroup As String = RootNode.SelectSingleNode("Student/StGroup").InnerText
        Dim StBank As Integer = RootNode.SelectSingleNode("Student/StBank").InnerText
        Dim StBranch As String = RootNode.SelectSingleNode("Student/StBranch").InnerText
        Dim StAccount As String = RootNode.SelectSingleNode("Student/StAccount").InnerText
        '
        Dim ResTel As String = RootNode.SelectSingleNode("Student/ResTel").InnerText
        Dim MobTel As String = RootNode.SelectSingleNode("Student/MobTel").InnerText
        Dim AddrLocal As String = RootNode.SelectSingleNode("Student/AddressLocal").InnerText
        Dim AddrForeign As String = RootNode.SelectSingleNode("Student/AddressForeign").InnerText
        Dim Degree As String = RootNode.SelectSingleNode("Student/Degree").InnerText
        Dim StCompany As String = RootNode.SelectSingleNode("Student/CompanyID").InnerText

        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim sqlCon2 As SqlConnection = New SqlConnection(strCon)

        sqlCon.Open()
        sqlCon2.Open()
        Dim strsql As String
        Dim SqlCmd As New SqlCommand()
        Dim SqlCmd2 As New SqlCommand()

        Dim sqlTrans As SqlTransaction
        sqlTrans = sqlCon.BeginTransaction
        SqlCmd.Transaction = sqlTrans
        Try
            Dim EmpID As Integer
            strsql = "Insert into Employee (EmpType,EmpCompany,EmpPfNumber,EmpName,EmpDesignation,EmpCIDnumber,EmpCIDexpiry," & _
                    "EmpPPnumber,EmpPPexpiry,EmpDOB,EmpMobile,EmpResPhone," & _
                    "EmpSex,EmpNationalityID,EmpAddressLocal,EmpAddressForeign,EmpAppointmentDate," & _
                    "EmpQualification,CreatedUserID,ModifiedUserID,EmpBankID,EmpBankBranch,EmpBankAccount,EmpCategory) " & _
                    "Values(" & StGroup & "," & StCompany & ",'" & RegNo & "','" & StName & "','" & StPost & "','" & StCid & "','" & StCidExpiry & "'," & _
                    "'" & StPnum & "','" & StPexpiry & "','" & StDob & "'," & _
                    "'" & MobTel & "','" & ResTel & "','" & StSex & "','" & StNationality & "','" & AddrLocal & "','" & AddrForeign & "'," & _
                    "'" & StAdmit & "','" & Degree & "'," & UserID & "," & UserID & "," & StBank & ",'" & StBranch & "','" & StAccount & "'," & StGroup & ") " & _
                    "Select @@identity"
            SqlCmd.CommandText = strsql
            EmpID = SqlCmd.ExecuteScalar
            If StPhotoPath.Trim <> "" Then
                Dim oImg As FileStream
                Dim oBinaryReader As BinaryReader
                Dim oImgByteArray As Byte()
                oImg = New FileStream(StPhotoPath, FileMode.Open, FileAccess.Read)
                oBinaryReader = New BinaryReader(oImg)
                oImgByteArray = oBinaryReader.ReadBytes(oImg.Length)
                Dim myParameter As SqlParameter = New SqlParameter("@Image", SqlDbType.Image, oImg.Length)
                myParameter.Value = oImgByteArray
                oBinaryReader.Close()
                oImg.Close()
                strsql = "Insert into EmployeePhoto " & _
                         "(EmpID,CreatedUserID,ModifiedUserID,EmpPhoto) " & _
                         "Values(" & EmpID & "," & UserID & "," & UserID & ",@Image)"
                SqlCmd.CommandText = strsql
                SqlCmd.Parameters.Add(myParameter)
                SqlCmd.ExecuteNonQuery()
            End If

        Catch ex As SqlException
            MsgBox(ex.ToString())
            AddNewEmployeeProfile = "ER"
            sqlTrans.Rollback()
            sqlCon.Close()
            Exit Function
        End Try
        sqlTrans.Commit()
        sqlCon.Close()
        AddNewEmployeeProfile = "OK"
    End Function
    Public Function UpdateEmployeeProfile(ByVal UserID As Integer, ByVal ProfileXml As String) As String
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(ProfileXml)
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim EmpID As Integer = RootNode.SelectSingleNode("Student/StID").InnerText
        Dim RegNo As Integer = RootNode.SelectSingleNode("Student/StRegNo").InnerText
        Dim StAdmit As String = ValidDate(RootNode.SelectSingleNode("Student/StAdmit").InnerText)
        Dim StName As String = RootNode.SelectSingleNode("Student/StName").InnerText
        Dim StPost As String = RootNode.SelectSingleNode("Student/StPost").InnerText
        Dim StCid As String = RootNode.SelectSingleNode("Student/StCid").InnerText
        Dim StCidExpiry As String = ValidDate(RootNode.SelectSingleNode("Student/StCidExpiry").InnerText)
        Dim StDob As String = ValidDate(RootNode.SelectSingleNode("Student/StDob").InnerText)
        Dim StPnum As String = RootNode.SelectSingleNode("Student/StPnum").InnerText
        Dim StPexpiry As String = ValidDate(RootNode.SelectSingleNode("Student/StPexpiry").InnerText)
        Dim StNationality As String = RootNode.SelectSingleNode("Student/StNationality").InnerText
        Dim StSex As String = RootNode.SelectSingleNode("Student/StSex").InnerText
        Dim StPhotoPath As String = RootNode.SelectSingleNode("Student/StPhotoPath").InnerText
        Dim StGroup As String = RootNode.SelectSingleNode("Student/StGroup").InnerText
        Dim StBank As String = RootNode.SelectSingleNode("Student/StBank").InnerText
        Dim StBranch As String = RootNode.SelectSingleNode("Student/StBranch").InnerText
        Dim StAccount As String = RootNode.SelectSingleNode("Student/StAccount").InnerText
        '
        Dim ResTel As String = RootNode.SelectSingleNode("Student/ResTel").InnerText
        Dim MobTel As String = RootNode.SelectSingleNode("Student/MobTel").InnerText
        Dim AddrLocal As String = RootNode.SelectSingleNode("Student/AddressLocal").InnerText
        Dim AddrForeign As String = RootNode.SelectSingleNode("Student/AddressForeign").InnerText
        Dim Degree As String = RootNode.SelectSingleNode("Student/Degree").InnerText
        Dim SpouseName As String = RootNode.SelectSingleNode("Student/SpouseName").InnerText
        '
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim sqlCon2 As SqlConnection = New SqlConnection(strCon)

        sqlCon.Open()
        sqlCon2.Open()
        Dim strsql As String
        Dim SqlCmd As New SqlCommand()
        Dim SqlCmd2 As New SqlCommand()

        Dim sqlTrans As SqlTransaction
        sqlTrans = sqlCon.BeginTransaction
        SqlCmd.Transaction = sqlTrans
        Try
            strsql = "Update Employee set EmpType='" & StGroup & "',EmpPfNumber='" & RegNo & "'," & _
                "EmpName='" & StName & "',EmpDesignation='" & StPost & "',EmpCidNumber='" & StCid & "',EmpCidExpiry='" & StCidExpiry & "'," & _
                "EmpPPnumber='" & StPnum & "',EmpPPexpiry='" & StPexpiry & "',EmpDOB='" & StDob & "'," & _
                "EmpCategory='" & StGroup & "',EmpMobile='" & MobTel & "',EmpResPhone='" & ResTel & "'," & _
                "EmpSex='" & StSex & "',EmpNationalityID='" & StNationality & "',EmpAddressLocal='" & AddrLocal & "'," & _
                "EmpAddressForeign='" & AddrForeign & "',EmpAppointmentDate='" & StAdmit & "'," & _
                "EmpQualification='" & Degree & "',EmpSpouseName='" & SpouseName & "',ModifiedUserID=" & UserID & _
                ",ModifiedDateTime=getdate(),EmpBankID=" & StBank & ",EmpBankBranch='" & StBranch & "',EmpBankAccount='" & StAccount & "' where EmpID=" & EmpID
            SqlCmd.CommandText = strsql
            SqlCmd.ExecuteNonQuery()
            If StPhotoPath.Trim <> "" Then
                Dim oImg As FileStream
                Dim oBinaryReader As BinaryReader
                Dim oImgByteArray As Byte()
                oImg = New FileStream(StPhotoPath, FileMode.Open, FileAccess.Read)
                oBinaryReader = New BinaryReader(oImg)
                oImgByteArray = oBinaryReader.ReadBytes(oImg.Length)
                Dim myParameter As SqlParameter = New SqlParameter("@Image", SqlDbType.Image, oImg.Length)
                myParameter.Value = oImgByteArray
                oBinaryReader.Close()
                oImg.Close()
                If EmployeePhotoExist(EmpID) = True Then
                    strsql = "Update EmployeePhoto set ModifiedDateTime=getdate(),ModifiedUserID=" & UserID & ",EmpPhoto=@Image where EmpID=" & EmpID
                Else
                    strsql = "Insert into EmployeePhoto " & _
                             "(EmpID,CreatedUserID,ModifiedUserID,EmpPhoto) " & _
                             "Values(" & EmpID & "," & UserID & "," & UserID & ",@Image)"
                End If
                SqlCmd.CommandText = strsql
                SqlCmd.Parameters.Add(myParameter)
                SqlCmd.ExecuteNonQuery()
            End If

        Catch ex As SqlException
            MsgBox(ex.ToString())
            UpdateEmployeeProfile = "ER"
            sqlTrans.Rollback()
            sqlCon.Close()
            Exit Function
        End Try
        sqlTrans.Commit()
        sqlCon.Close()
        UpdateEmployeeProfile = "OK"
    End Function
    Public Function EmployeePhotoExist(ByVal EmpID As Integer) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select count(*) from EmployeePhoto with (NoLock) where EmpID=" & EmpID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim mcount As Integer
        mcount = sqlCmd.ExecuteScalar
        If mcount = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

    Public Function GetEmployeePhoto(ByVal EmpID As Integer) As Byte()
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select EmpPhoto from EmployeePhoto where EmpID=" & EmpID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim barray As Byte()

        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            If Not IsDBNull(rdr("EmpPhoto")) Then
                barray = rdr("EmpPhoto")
            End If
        End While
        Return barray
    End Function
    Public Sub InsertBenefit(ByVal PfNumber As String, ByVal Salary As String, _
                ByVal Allowance As String, ByVal HRA As String, ByVal Trans As String, ByVal OtFlag As Integer, _
                ByVal Loan1 As String, ByVal Loan2 As String, ByVal Loan3 As String, ByVal Loan4 As String, _
                ByVal Inst1 As String, ByVal Inst2 As String, ByVal Inst3 As String, ByVal Inst4 As String, ByVal VacDays As String, ByVal WorkDays As String, _
                ByVal Remark1 As String, ByVal Remark2 As String, ByVal Remark3 As String, ByVal Ticket As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim EmpID As Integer = GetEmployeeID(PfNumber)
        strsql = "Insert into EmployeeBenefit (EmpID,BasicSalary,Allowance,HouseRent," & _
                "Conveyance,EligibleForOvertime,Loan1Amount,Loan2Amount,Loan3Amount,Loan4Amount," & _
                "Loan1Installment,Loan2Installment,Loan3Installment,Loan4Installment,AnnualVacationDays,DaysWorked,Remark1,Remark2,Remark3,AirTicketFare,OvertimeAmount) " & _
                "values(" & EmpID & ",'" & Encrypt(PrefixInteger(Salary)) & "','" & Encrypt(PrefixInteger(Allowance)) & "','" & _
                Encrypt(PrefixInteger(HRA)) & "','" & Encrypt(PrefixInteger(Trans)) & "','" & PrefixInteger(OtFlag) & "','" & Encrypt(PrefixInteger(Loan1)) & "','" & _
                Encrypt(PrefixInteger(Loan2)) & "','" & Encrypt(PrefixInteger(Loan3)) & "','" & Encrypt(PrefixInteger(Loan4)) & "','" & _
                Encrypt(PrefixInteger(Inst1)) & "','" & Encrypt(PrefixInteger(Inst2)) & "','" & Encrypt(PrefixInteger(Inst2)) & "','" & Encrypt(PrefixInteger(Inst4)) & "','" & PrefixInteger(VacDays) & "'," & PrefixInteger(WorkDays) & ",'" & Remark1 & "','" & Remark2 & "','" & Remark3 & "','" & Encrypt(PrefixInteger(Ticket)) & "','" & Encrypt("0") & "')"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub InsertVacation(ByVal PfNumber As String, ByVal VacationType As String, _
             ByVal VacationFrom As String, ByVal VacationTo As String, ByVal WSdays As String, _
             ByVal WSamount As String, ByVal WOdays As String, ByVal GrossServiceDays As String, ByVal UnpaidVacationDays As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim EmpID As Integer = GetEmployeeID(PfNumber)
        strsql = "Insert into EmployeeVacation (EmpID,VacationType,VacationFrom,VacationTo," & _
                "VacWSdays,VacWSamount,VacWOdays,GrossServiceDays,UnpaidVacationDays,CreatedBy) " & _
                "values(" & EmpID & ",'" & VacationType & "','" & ValidDate(VacationFrom) & "','" & _
                ValidDate(VacationTo) & "'," & PrefixInteger(WSdays) & "," & PrefixInteger(WSamount) & "," & _
                PrefixInteger(WOdays) & "," & PrefixInteger(GrossServiceDays) & "," & PrefixInteger(UnpaidVacationDays) & "," & UserID & ")"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub InsertIndemnity(ByVal PfNumber As String, ByVal TranType As String, _
         ByVal ProcessDate As String, ByVal GrossServiceDays As String, _
         ByVal UnpaidVacationDays As String, ByVal TranAmount As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim EmpID As Integer = GetEmployeeID(PfNumber)
        strsql = "Insert into EmployeeVacation (EmpID,TranDate,TranType,Amount,GrossServiceDays," & _
                "UnpaidVacationDays,CreatedBy) " & _
                "values(" & EmpID & ",'" & ValidDate(ProcessDate) & "','" & _
                TranType & "'," & PrefixInteger(TranAmount) & "," & PrefixInteger(GrossServiceDays) & "," & _
                PrefixInteger(UnpaidVacationDays) & "," & UserID & ")"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        If TranType = "R" Then
            TranType = "RESIGN"
        Else
            TranType = "TERMIN"
        End If
        sqlCmd.CommandText = "Update Employee set EmpStatus='" & TranType & "' where EmpID=" & EmpID
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub UpdateWeeklyOff(ByVal EmpID As Integer, ByVal Sat As Integer, ByVal Sun As Integer, ByVal Mon As Integer, _
                            ByVal Tue As Integer, ByVal Wed As Integer, ByVal Thu As Integer, ByVal Fri As Integer, ByVal OffDay As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim sqlCon2 As SqlConnection = New SqlConnection(strCon)

        Dim sqlCmd As New SqlCommand()
        Dim sqlCmd2 As New SqlCommand()
        sqlCmd2.CommandText = "Select count(*) from EmployeeWeeklyOff where EmpID=" & EmpID
        sqlCon2.Open()
        Dim OffExist As Boolean = False
        Try
            If sqlCmd2.ExecuteScalar > 0 Then
                OffExist = True
            End If
        Catch ex As Exception
            OffExist = False
        End Try
        If OffExist = False Then
            sqlCmd.CommandText = "insert into EmployeeWeeklyOff (EmpID,Sat,Sun,Mon,Tue,Wed,Thu,Fri,OffDay) " & _
                    "values (" & EmpID & "," & Sat & "," & Sun & "," & Mon & "," & Tue & "," & Wed & "," & Thu & "," & Fri & "," & OffDay & ")"
        Else
            sqlCmd.CommandText = "update EmployeeWeeklyOff set Sat=" & Sat & ",Sun=" & Sun & ",Mon=" & Mon & "," & _
                "Tue=" & Tue & ",Wed=" & Wed & ",Thu=" & Thu & ",Fri=" & Fri & ",OffDay=" & OffDay & " where EmpID=" & EmpID
        End If
        sqlCon.Open()
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub UpdateBenefit(ByVal PfNumber As String, ByVal Salary As String, _
            ByVal Allowance As String, ByVal HRA As String, ByVal Trans As String, ByVal OtFlag As Integer, _
            ByVal Loan1 As String, ByVal Loan2 As String, ByVal Loan3 As String, ByVal Loan4 As String, _
            ByVal Inst1 As String, ByVal Inst2 As String, ByVal Inst3 As String, ByVal Inst4 As String, ByVal VacDays As String, ByVal WorkDays As String, ByVal Remark1 As String, ByVal Remark2 As String, ByVal Remark3 As String, ByVal Ticket As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim EmpID As Integer = GetEmployeeID(PfNumber)
        strsql = "Update EmployeeBenefit set BasicSalary='" & Encrypt(PrefixInteger(Salary)) & "',Allowance='" & Encrypt(PrefixInteger(Allowance)) & "',HouseRent='" & Encrypt(PrefixInteger(HRA)) & "'," & _
                "EligibleForOvertime=" & PrefixInteger(OtFlag) & ",Conveyance='" & Encrypt(PrefixInteger(Trans)) & "',Loan1Amount='" & Encrypt(PrefixInteger(Loan1)) & "',Loan2Amount='" & Encrypt(PrefixInteger(Loan2)) & "',Loan3Amount='" & Encrypt(PrefixInteger(Loan3)) & "',Loan4Amount='" & Encrypt(PrefixInteger(Loan4)) & "'," & _
                "Loan1Installment='" & Encrypt(PrefixInteger(Inst1)) & "',Loan2Installment='" & Encrypt(PrefixInteger(Inst2)) & "',Loan3Installment='" & Encrypt(PrefixInteger(Inst3)) & "',Loan4Installment='" & Encrypt(PrefixInteger(Inst4)) & "',AnnualVacationDays=" & PrefixInteger(VacDays) & "," & _
                "Remark1='" & Remark1 & "',Remark2='" & Remark2 & "',Remark3='" & Remark3 & "',AirTicketFare='" & Encrypt(PrefixInteger(Ticket)) & "',DaysWorked=" & PrefixInteger(WorkDays) & _
                " where EmpID=" & EmpID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Function ProcessPayroll(ByVal ThisCompanyID As Integer, ByVal ProcessUser As Integer, ByVal InvXml As String) As String
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(InvXml)
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim PayMonth As Integer = RootNode.SelectSingleNode("PayHead/PayMonth").InnerText
        Dim PayYear As Integer = RootNode.SelectSingleNode("PayHead/PayYear").InnerText
        Dim PayDays As Integer = PrefixInteger(RootNode.SelectSingleNode("PayHead/PayDays").InnerText)
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("PayLine")
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim sqlCon2 As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlCmd As New SqlCommand()
        Dim sqlCmd2 As New SqlCommand()
        sqlCmd.Connection = sqlCon
        sqlCon.Open()
        sqlCmd2.Connection = sqlCon2
        sqlCon2.Open()
        Dim sqlTrans As SqlTransaction
        sqlTrans = sqlCon.BeginTransaction
        sqlCmd.Transaction = sqlTrans
        Dim PayProcessed As Boolean = False
        If IsPayrollProcessed(PayMonth, PayYear, ThisCompanyID) = True Then
            PayProcessed = True
        End If
        Dim i As Integer
        Dim TotCost As Double = 0
        For i = 0 To nodeList.Count - 1
            Dim EmpID As Integer = nodeList.Item(i).ChildNodes(0).InnerText
            Dim Salary As String = nodeList.Item(i).ChildNodes(1).InnerText
            Dim Allowance As String = PrefixInteger(nodeList.Item(i).ChildNodes(2).InnerText)
            Dim HouseRent As String = PrefixInteger(nodeList.Item(i).ChildNodes(3).InnerText)
            Dim Transport As String = PrefixInteger(nodeList.Item(i).ChildNodes(4).InnerText)
            Dim Overtime As String = PrefixInteger(nodeList.Item(i).ChildNodes(5).InnerText)
            Dim GrossAmount As String = PrefixInteger(nodeList.Item(i).ChildNodes(6).InnerText)
            Dim Loan1Inst As String = PrefixInteger(nodeList.Item(i).ChildNodes(7).InnerText)
            Dim Loan2Inst As String = PrefixInteger(nodeList.Item(i).ChildNodes(8).InnerText)
            Dim Loan3Inst As String = PrefixInteger(nodeList.Item(i).ChildNodes(9).InnerText)
            Dim Loan4Inst As String = PrefixInteger(nodeList.Item(i).ChildNodes(10).InnerText)
            Dim Prorata As String = PrefixInteger(nodeList.Item(i).ChildNodes(11).InnerText)
            Dim TotDeduction As String = PrefixInteger(nodeList.Item(i).ChildNodes(12).InnerText)
            Dim NetAmount As String = PrefixInteger(nodeList.Item(i).ChildNodes(13).InnerText)
            Dim DaysWorked As String = PrefixInteger(nodeList.Item(i).ChildNodes(14).InnerText)

            Dim Cap1, Cap2, Cap3, Cap4 As Decimal
            GetLoanAmounts(EmpID, Cap1, Cap2, Cap3, Cap4)
            'Cap1 = Decrypt(Cap1)
            'Cap2 = Decrypt(Cap2)
            'Cap3 = Decrypt(Cap3)
            'Cap4 = Decrypt(Cap4)

            Try
                Dim Inst1, Inst2, Inst3, Inst4 As Decimal
                Dim PayID As Integer = 0
                If PayProcessed = True Then
                    PayID = GetDeductedAmounts(EmpID, PayMonth, PayYear, Inst1, Inst2, Inst3, Inst4)
                    'Inst1 = Decrypt(Inst1)
                    'Inst2 = Decrypt(Inst2)
                    'Inst3 = Decrypt(Inst3)
                    'Inst4 = Decrypt(Inst4)

                    sqlCmd.CommandText = "Update EmployeeBenefit " & _
                        "set Loan1Amount='" & Cap1 + Inst1 & "',Loan2Amount='" & Cap2 + Inst2 & _
                        "',Loan3Amount='" & Cap3 + Inst3 & "',Loan4Amount='" & Cap4 + Inst4 & _
                        "' where EmpID=" & EmpID
                    sqlCmd.ExecuteNonQuery()
                    strsql = "Update EmployeePayroll set DaysWorked='" & DaysWorked & "',BasicSalary='" & Encrypt(Salary) & "'," & _
                        "Allowance='" & Encrypt(Allowance) & "',HouseRent='" & Encrypt(HouseRent) & "',Conveyance='" & Encrypt(Transport) & "'," & _
                        "OvertimeAmount='" & Encrypt(Overtime) & "',GrossAmount='" & Encrypt(GrossAmount) & "',Deduction1='" & Encrypt(Loan1Inst) & "'," & _
                        "Deduction2='" & Encrypt(Loan2Inst) & "',Deduction3='" & Encrypt(Loan3Inst) & "',ProrataDeduction='" & Encrypt(Prorata) & "'," & _
                        "TotalDeduction='" & Encrypt(TotDeduction) & "',NetAmount='" & Encrypt(NetAmount) & "',TotalDays='" & PayDays & "'," & _
                        "UpdatedUserID='" & ProcessUser & "',ProcessDate=getdate() where PayrollID=" & PayID
                    sqlCmd.CommandText = strsql
                    sqlCmd.ExecuteNonQuery()
                Else
                    strsql = "Insert into EmployeePayroll (CompanyID,MonthCode,YearCode,EmpID,DaysWorked,BasicSalary,Allowance," & _
                        "HouseRent,Conveyance,OvertimeAmount,GrossAmount,Deduction1,Deduction2,Deduction3," & _
                        "Deduction4,ProrataDeduction,TotalDeduction,NetAmount,TotalDays,UpdatedUserID) " & _
                        "values('" & ThisCompanyID & "','" & PayMonth & "','" & PayYear & "','" & EmpID & "','" & DaysWorked & "','" & _
                        Encrypt(Salary) & "','" & Encrypt(Allowance) & "','" & Encrypt(HouseRent) & "','" & Encrypt(Transport) & "','" & _
                        Encrypt(Overtime) & "','" & Encrypt(GrossAmount) & "','" & Encrypt(Loan1Inst) & "','" & Encrypt(Loan2Inst) & "','" & Encrypt(Loan3Inst) & "','" & _
                        Encrypt(Loan4Inst) & "','" & Encrypt(Prorata) & "','" & Encrypt(TotDeduction) & "','" & Encrypt(NetAmount) & "','" & PayDays & "','" & ProcessUser & "') "
                    sqlCmd.CommandText = strsql
                    sqlCmd.ExecuteNonQuery()
                End If

                If Cap1 > 0 Then
                    If Cap1 < Loan1Inst Then
                        Loan1Inst = Cap1
                    End If
                    sqlCmd.CommandText = "Update EmployeeBenefit " & _
                        "set Loan1Amount='" & Encrypt(Cap1 - Loan1Inst) & "' where EmpID=" & EmpID
                    sqlCmd.ExecuteNonQuery()
                End If
                If Cap2 > 0 Then
                    If Cap2 < Loan2Inst Then
                        Loan2Inst = Cap2
                    End If
                    sqlCmd.CommandText = "Update EmployeeBenefit " & _
                        "set Loan2Amount='" & Encrypt(Cap2 - Loan2Inst) & "' where EmpID=" & EmpID
                    sqlCmd.ExecuteNonQuery()
                End If
                If Cap3 > 0 Then
                    If Cap3 < Loan3Inst Then
                        Loan3Inst = Cap3
                    End If
                    sqlCmd.CommandText = "Update EmployeeBenefit " & _
                        "set Loan3Amount='" & Encrypt(Cap3 - Loan3Inst) & "' where EmpID=" & EmpID
                    sqlCmd.ExecuteNonQuery()
                End If
                If Cap4 > 0 Then
                    If Cap4 < Loan4Inst Then
                        Loan4Inst = Cap4
                    End If
                    sqlCmd.CommandText = "Update EmployeeBenefit " & _
                        "set Loan4Amount='" & Encrypt(Cap4 - Loan4Inst) & "' where EmpID=" & EmpID
                    sqlCmd.ExecuteNonQuery()
                End If
            Catch ex3 As SqlException
                sqlTrans.Rollback()
                Return "ERROR"
            End Try
        Next i
        sqlTrans.Commit()
        sqlCon.Close()
        Return "OK"
    End Function
    Public Function ValidDate(ByVal strVal As String) As String
        If strVal.Trim = "" Then
            Return "0"
        Else
            Dim dd As String
            Dim mm As String
            Dim yyyy As String
            dd = strVal.Substring(0, 2)
            mm = strVal.Substring(3, 2)
            yyyy = strVal.Substring(6, 4)
            strVal = mm & "/" & dd & "/" & yyyy
            Return strVal
        End If
    End Function
    Public Function PrefixInteger(ByVal strVal As String) As String
        If strVal.Trim = "" Then
            Return "0"
        Else
            Return strVal.Replace(",", "")
        End If
    End Function
    Public Function GetMonthCode(ByVal MonthName As String) As Integer
        Dim MonthCode As Integer = 1
        Select Case MonthName
            Case Is = "January"
                MonthCode = 1
            Case Is = "February"
                MonthCode = 2
            Case Is = "March"
                MonthCode = 3
            Case Is = "April"
                MonthCode = 4
            Case Is = "May"
                MonthCode = 5
            Case Is = "June"
                MonthCode = 6
            Case Is = "July"
                MonthCode = 7
            Case Is = "August"
                MonthCode = 8
            Case Is = "September"
                MonthCode = 9
            Case Is = "October"
                MonthCode = 10
            Case Is = "November"
                MonthCode = 11
            Case Is = "December"
                MonthCode = 12
        End Select
        Return MonthCode
    End Function
    Public Function Encrypt(ByVal PlainText As String) As String
        Dim utf8Encoder As UTF8Encoding = New UTF8Encoding
        Dim inputInBytes() As Byte = utf8Encoder.GetBytes(PlainText)
        Dim tdesProvider As TripleDESCryptoServiceProvider = New TripleDESCryptoServiceProvider
        Dim encryptedStream As MemoryStream = New MemoryStream
        Dim cryptoTransform As ICryptoTransform = tdesProvider.CreateEncryptor(Me.key, HuRekey)
        Dim cryptStream As CryptoStream = New CryptoStream(encryptedStream, cryptoTransform, CryptoStreamMode.Write)
        cryptStream.Write(inputInBytes, 0, inputInBytes.Length)
        cryptStream.FlushFinalBlock()
        encryptedStream.Position = 0
        Dim result(encryptedStream.Length - 1) As Byte
        encryptedStream.Read(result, 0, encryptedStream.Length)
        cryptStream.Close()
        Dim EncrText As String
        'EncrText = utf8Encoder.GetString(result)
        EncrText = Convert.ToBase64String(result)
        Return EncrText
    End Function
    Public Function Decrypt(ByVal EncrText As String) As String
        Dim utf8Encoder As UTF8Encoding = New UTF8Encoding
        Dim inputInBytes() As Byte = Convert.FromBase64String(EncrText)
        Dim tdesProvider As TripleDESCryptoServiceProvider = New TripleDESCryptoServiceProvider
        Dim decryptedStream As MemoryStream = New MemoryStream
        Dim cryptoTransform As ICryptoTransform = tdesProvider.CreateDecryptor(Me.key, HuRekey)
        Dim cryptStream As CryptoStream = New CryptoStream(decryptedStream, cryptoTransform, CryptoStreamMode.Write)
        cryptStream.Write(inputInBytes, 0, inputInBytes.Length)
        cryptStream.FlushFinalBlock()
        decryptedStream.Position = 0
        Dim result(decryptedStream.Length - 1) As Byte
        decryptedStream.Read(result, 0, decryptedStream.Length)
        cryptStream.Close()
        Dim DecrText As String
        DecrText = utf8Encoder.GetString(result)
        Return DecrText
    End Function
End Class
